USP_CORPORATESTRUCTURE_GETREVENUESUMMARY

            -- Expects that a temp table titled #TMP_CORPORATESTRUCTURE_REVENUESUMMARY exists

            -- and it has the fields:

            -- ID (used for input) - The ID of the corporation constituent record

            -- ISHISTORICAL (used for input) - Indicates if the corporate record is historical or not

            -- ENDDATE (used for input) - Used for historical records

            -- CORPORATIONGIVINGAMOUNT (used for output)

            -- CORPORATIONGIVINGCOUNT (used for output)

            -- EMPLOYEEGIVINGAMOUNT (used for output)

            -- EMPLOYEEGIVINGCOUNT (used for output)

            CREATE procedure dbo.USP_CORPORATESTRUCTURE_GETREVENUESUMMARY
            (
                @CURRENTAPPUSERID uniqueidentifier,
                @SECURITYFEATUREID uniqueidentifier,
                @SECURITYFEATURETYPE tinyint,
                @STARTDATE datetime,
                @ENDDATE datetime,
                @SELECTEDCURRENCYID uniqueidentifier,
                @ORGANIZATIONCURRENCYID uniqueidentifier,
                @CURRENCYCODE tinyint,
                @CURRENCYORIGINCODE tinyint,
                @CURRENCYDECIMALDIGITS tinyint,
                @CURRENCYROUNDINGTYPECODE tinyint
            )
            as
            begin
                set nocount on;

                declare @HASSITES bit = 0;
                declare @ISADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

                if exists (select top 1 1 from dbo.SITE)
                    set @HASSITES = 1 

                --Sites temp table just used for non-admins, only create if needed

                if @ISADMIN = 0 and @HASSITES = 1
                begin
                    -- Set the corporation and employee giving for the corporations

                    if object_id('tempdb..#TMP_CORPORATESTRUCTURE_SITEONFEATURE') is not null  
                        drop table #TMP_CORPORATESTRUCTURE_SITEONFEATURE

                    create table #TMP_CORPORATESTRUCTURE_SITEONFEATURE
                    (
                        ID uniqueidentifier
                    )

                    insert into #TMP_CORPORATESTRUCTURE_SITEONFEATURE (ID)
                    select
                        distinct FEATURESITE.SITEID 
                    from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) FEATURESITE
                    inner join dbo.UFN_SITEID_MAPFROM_APPUSERID(@CURRENTAPPUSERID) USERMAP on isnull(FEATURESITE.SITEID, '00000000-0000-0000-0000-000000000000') = isnull(USERMAP.SITEID, '00000000-0000-0000-0000-000000000000')
                end

                -- Materialize the valid revenue for the corporations and their employees.

                if object_id('tempdb..#TMP_CORPORATESTRUCTURE_CORPORATIONREVENUE') is not null  
                    drop table #TMP_CORPORATESTRUCTURE_CORPORATIONREVENUE

                create table #TMP_CORPORATESTRUCTURE_CORPORATIONREVENUE
                (
                    ID uniqueidentifier,
                    CONSTITUENTID uniqueidentifier,
                    TRANSACTIONTYPECODE tinyint,
                    AMOUNT money
                )

                if @ISADMIN = 0 and @HASSITES = 1
                begin
                    insert into #TMP_CORPORATESTRUCTURE_CORPORATIONREVENUE (ID, CONSTITUENTID, TRANSACTIONTYPECODE, AMOUNT)
                    select distinct
                        REVENUE.ID,
                        CORPORATESTRUCTURE.ID,
                        REVENUE.TYPECODE,
                        REVENUE.TRANSACTIONAMOUNT
                    from #TMP_CORPORATESTRUCTURE_REVENUESUMMARY CORPORATESTRUCTURE
                    inner join dbo.FINANCIALTRANSACTION REVENUE on CORPORATESTRUCTURE.ID = REVENUE.CONSTITUENTID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
                    inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
                    cross apply
                    (
                        select case CORPORATESTRUCTURE.ISHISTORICAL
                                when 0 then @ENDDATE
                                else
                                    case
                                        when CORPORATESTRUCTURE.ENDDATE is null then @ENDDATE
                                        when @ENDDATE is null then CORPORATESTRUCTURE.ENDDATE
                                        when CORPORATESTRUCTURE.ENDDATE <= @ENDDATE then CORPORATESTRUCTURE.ENDDATE
                                        else @ENDDATE
                                    end
                            end as ENDDATE
                    ) as CORPORATEENDDATE
                    where 
                        REVENUE.DELETEDON is null and
                        (@STARTDATE is null or REVENUE.DATE >= @STARTDATE) and            
                        (CORPORATESTRUCTURE.ENDDATE is null or cast(REVENUE.DATE as datetime) <= CORPORATESTRUCTURE.ENDDATE) and
                        REVENUE.TYPECODE in (0,1,3) --Payment, Pledge or MG claim

                        and exists(select 1 from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
                                   inner join #TMP_CORPORATESTRUCTURE_SITEONFEATURE SITEONFEATURE on isnull(REVSITES.SITEID, '00000000-0000-0000-0000-000000000000') = isnull(SITEONFEATURE.ID, '00000000-0000-0000-0000-000000000000'))
                end
                else
                begin
                    insert into #TMP_CORPORATESTRUCTURE_CORPORATIONREVENUE (ID, CONSTITUENTID, TRANSACTIONTYPECODE, AMOUNT)
                    select distinct
                        REVENUE.ID,
                        CORPORATESTRUCTURE.ID,
                        REVENUE.TYPECODE,
                        REVENUE.TRANSACTIONAMOUNT
                    from #TMP_CORPORATESTRUCTURE_REVENUESUMMARY CORPORATESTRUCTURE
                    inner join dbo.FINANCIALTRANSACTION REVENUE on CORPORATESTRUCTURE.ID = REVENUE.CONSTITUENTID
                    inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
                    cross apply
                    (
                        select case CORPORATESTRUCTURE.ISHISTORICAL
                                when 0 then @ENDDATE
                                else
                                    case
                                        when CORPORATESTRUCTURE.ENDDATE is null then @ENDDATE
                                        when @ENDDATE is null then CORPORATESTRUCTURE.ENDDATE
                                        when CORPORATESTRUCTURE.ENDDATE <= @ENDDATE then CORPORATESTRUCTURE.ENDDATE
                                        else @ENDDATE
                                    end
                            end as ENDDATE
                    ) as CORPORATEENDDATE
                    where 
                        REVENUE.DELETEDON is null and
                        (@STARTDATE is null or REVENUE.DATE >= @STARTDATE) and            
                        (CORPORATESTRUCTURE.ENDDATE is null or cast(REVENUE.DATE as datetime) <= CORPORATESTRUCTURE.ENDDATE) and
                        REVENUE.TYPECODE in (0,1,3) --Payment, Pledge or MG claim

                end

                if object_id('tempdb..#TMP_CORPORATESTRUCTURE_EMPLOYEEREVENUE') is not null  
                    drop table #TMP_CORPORATESTRUCTURE_EMPLOYEEREVENUE

                create table #TMP_CORPORATESTRUCTURE_EMPLOYEEREVENUE
                (
                    ID uniqueidentifier,
                    EMPLOYERID uniqueidentifier,
                    TRANSACTIONTYPECODE tinyint,
                    AMOUNT money
                )

                if @ISADMIN = 0 and @HASSITES = 1
                begin
                    insert into #TMP_CORPORATESTRUCTURE_EMPLOYEEREVENUE (ID, EMPLOYERID, TRANSACTIONTYPECODE, AMOUNT)
                    select distinct
                        REVENUE.ID,
                        RELATIONSHIP.RECIPROCALCONSTITUENTID,
                        REVENUE.TYPECODE,
                        REVENUE.TRANSACTIONAMOUNT
                    from #TMP_CORPORATESTRUCTURE_REVENUESUMMARY CORPORATESTRUCTURE 
                    inner join dbo.RELATIONSHIP on RELATIONSHIP.RECIPROCALCONSTITUENTID = CORPORATESTRUCTURE.ID
                    inner join dbo.EMPLOYEECORPORATERELATIONSHIPTYPE on RELATIONSHIP.RELATIONSHIPTYPECODEID = EMPLOYEECORPORATERELATIONSHIPTYPE.RELATIONSHIPTYPECODEID
                    inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
                    inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
                    cross apply
                    (
                        select case CORPORATESTRUCTURE.ISHISTORICAL
                                when 0 then @ENDDATE
                                else
                                    case
                                        when CORPORATESTRUCTURE.ENDDATE is null then @ENDDATE
                                        when @ENDDATE is null then CORPORATESTRUCTURE.ENDDATE
                                        when CORPORATESTRUCTURE.ENDDATE <= @ENDDATE then CORPORATESTRUCTURE.ENDDATE
                                        else @ENDDATE
                                    end
                            end as ENDDATE
                    ) as CORPORATEENDDATE
                    where
                        REVENUE.DELETEDON is null and
                        (@STARTDATE is null or cast(REVENUE.DATE as datetime) >= @STARTDATE) and
                        (CORPORATEENDDATE.ENDDATE is null or cast(REVENUE.DATE as datetime) <= CORPORATEENDDATE.ENDDATE) and
                        REVENUE.TYPECODE in (0,1,3) --Payment, Pledge or MG claim

                        and exists(select 1 from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
                                   inner join #TMP_CORPORATESTRUCTURE_SITEONFEATURE SITEONFEATURE on isnull(REVSITES.SITEID, '00000000-0000-0000-0000-000000000000') = isnull(SITEONFEATURE.ID, '00000000-0000-0000-0000-000000000000'))

                end
                else
                begin
                    insert into #TMP_CORPORATESTRUCTURE_EMPLOYEEREVENUE (ID, EMPLOYERID, TRANSACTIONTYPECODE, AMOUNT)
                    select distinct
                        REVENUE.ID,
                        RELATIONSHIP.RECIPROCALCONSTITUENTID,
                        REVENUE.TYPECODE,
                        REVENUE.TRANSACTIONAMOUNT
                    from #TMP_CORPORATESTRUCTURE_REVENUESUMMARY CORPORATESTRUCTURE 
                    inner join dbo.RELATIONSHIP on RELATIONSHIP.RECIPROCALCONSTITUENTID = CORPORATESTRUCTURE.ID
                    inner join dbo.EMPLOYEECORPORATERELATIONSHIPTYPE on RELATIONSHIP.RELATIONSHIPTYPECODEID = EMPLOYEECORPORATERELATIONSHIPTYPE.RELATIONSHIPTYPECODEID
                    inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                    inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
                    cross apply
                    (
                        select case CORPORATESTRUCTURE.ISHISTORICAL
                                when 0 then @ENDDATE
                                else
                                    case
                                        when CORPORATESTRUCTURE.ENDDATE is null then @ENDDATE
                                        when @ENDDATE is null then CORPORATESTRUCTURE.ENDDATE
                                        when CORPORATESTRUCTURE.ENDDATE <= @ENDDATE then CORPORATESTRUCTURE.ENDDATE
                                        else @ENDDATE
                                    end
                            end as ENDDATE
                    ) as CORPORATEENDDATE
                    where
                        REVENUE.DELETEDON is null and
                        (@STARTDATE is null or cast(REVENUE.DATE as datetime) >= @STARTDATE) and
                        (CORPORATEENDDATE.ENDDATE is null or cast(REVENUE.DATE as datetime) <= CORPORATEENDDATE.ENDDATE) and
                        REVENUE.TYPECODE in (0,1,3) --Payment, Pledge or MG claim

                end

                if dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency') = 1
                    update #TMP_CORPORATESTRUCTURE_REVENUESUMMARY set
                        CORPORATIONGIVINGAMOUNT = coalesce(CORPORATIONGIVING.AMOUNT, 0),
                        CORPORATIONGIVINGCOUNT = coalesce(CORPORATIONGIVING.[COUNT], 0),
                        EMPLOYEEGIVINGAMOUNT = coalesce(EMPLOYEEGIVING.AMOUNT, 0),
                        EMPLOYEEGIVINGCOUNT = coalesce(EMPLOYEEGIVING.[COUNT], 0)
                    from #TMP_CORPORATESTRUCTURE_REVENUESUMMARY CORPORATESTRUCTURE
                    left join
                    (
                        select
                            CORPORATIONREVENUE.CONSTITUENTID,
                            coalesce(sum(REVENUESPLIT.AMOUNTINCURRENCY), 0) - coalesce(sum(INSTALLMENTWRITEOFF.AMOUNT), 0) as AMOUNT,
                            count(distinct CORPORATIONREVENUE.ID) as [COUNT]
                        from #TMP_CORPORATESTRUCTURE_CORPORATIONREVENUE CORPORATIONREVENUE
                        inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) REVENUESPLIT on CORPORATIONREVENUE.ID = REVENUESPLIT.REVENUEID
                        left join 
                        (            
                            select
                                INSTALLMENTSPLIT.PLEDGEID,
                                INSTALLMENTSPLIT.DESIGNATIONID,
                                sum(IWO.AMOUNTINCURRENCY) as AMOUNT
                            from dbo.INSTALLMENTSPLIT 
                            inner join dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE, @CURRENCYORIGINCODE, @CURRENCYCODE) IWO on IWO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                            group by             
                                INSTALLMENTSPLIT.PLEDGEID, 
                                INSTALLMENTSPLIT.DESIGNATIONID            
                        ) INSTALLMENTWRITEOFF on INSTALLMENTWRITEOFF.PLEDGEID = REVENUESPLIT.REVENUEID and INSTALLMENTWRITEOFF.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID       
                        where
                            CORPORATIONREVENUE.TRANSACTIONTYPECODE in (1,3) or --Pledge or MG claim

                            (            
                                --Payment

                                CORPORATIONREVENUE.TRANSACTIONTYPECODE = 0 and 
                                (            
                                    REVENUESPLIT.APPLICATIONCODE in (0,1,3,4,5,6,18,19) or 
                                    (
                                        -- unapplied MG claim payments            

                                        REVENUESPLIT.APPLICATIONCODE = 7 and 
                                        not exists 
                                        (
                                            select 1 
                                            from dbo.INSTALLMENTSPLITPAYMENT 
                                            where REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                                        )
                                    )
                                )            
                            )
                        group by 
                            CORPORATIONREVENUE.CONSTITUENTID
                    ) as CORPORATIONGIVING on CORPORATESTRUCTURE.ID = CORPORATIONGIVING.CONSTITUENTID 
                    left join
                    (
                        select 
                            EMPLOYEEREVENUE.EMPLOYERID,
                            sum(REVENUESPLIT.AMOUNTINCURRENCY) as AMOUNT,
                            count(distinct EMPLOYEEREVENUE.ID) as [COUNT]
                        from #TMP_CORPORATESTRUCTURE_EMPLOYEEREVENUE EMPLOYEEREVENUE
                        inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) REVENUESPLIT on EMPLOYEEREVENUE.ID = REVENUESPLIT.REVENUEID
                        where
                            EMPLOYEEREVENUE.TRANSACTIONTYPECODE in (1,3) or --Pledge or MG claim

                            (            
                                --Payment

                                EMPLOYEEREVENUE.TRANSACTIONTYPECODE = 0 and 
                                (            
                                    REVENUESPLIT.APPLICATIONCODE in (0,1,3,4,5,6,18,19) or 
                                    (
                                        -- unapplied MG claim payments            

                                        REVENUESPLIT.APPLICATIONCODE = 7 and 
                                        not exists 
                                        (
                                            select 1 
                                            from dbo.INSTALLMENTSPLITPAYMENT 
                                            where REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                                        )
                                    )
                                )            
                            )
                        group by 
                            EMPLOYEEREVENUE.EMPLOYERID
                    ) as EMPLOYEEGIVING on CORPORATESTRUCTURE.ID = EMPLOYEEGIVING.EMPLOYERID
                else
                    update #TMP_CORPORATESTRUCTURE_REVENUESUMMARY set
                        CORPORATIONGIVINGAMOUNT = coalesce(CORPORATIONGIVING.AMOUNT, 0),
                        CORPORATIONGIVINGCOUNT = coalesce(CORPORATIONGIVING.[COUNT], 0),
                        EMPLOYEEGIVINGAMOUNT = coalesce(EMPLOYEEGIVING.AMOUNT, 0),
                        EMPLOYEEGIVINGCOUNT = coalesce(EMPLOYEEGIVING.[COUNT], 0)
                    from #TMP_CORPORATESTRUCTURE_REVENUESUMMARY CORPORATESTRUCTURE
                    left join
                    (
                        select
                            CORPORATIONREVENUE.CONSTITUENTID,
                            coalesce(sum(REVENUESPLIT.AMOUNTINCURRENCY), 0) - coalesce(sum(INSTALLMENTWRITEOFF.AMOUNT), 0) as AMOUNT,
                            count(distinct CORPORATIONREVENUE.ID) as [COUNT]
                        from #TMP_CORPORATESTRUCTURE_CORPORATIONREVENUE CORPORATIONREVENUE
                        inner join
                        (
                            select LI.ID
                                ,LI.FINANCIALTRANSACTIONID [REVENUEID]
                                ,LI.TRANSACTIONAMOUNT [AMOUNTINCURRENCY]
                                ,RSE.APPLICATIONCODE
                                ,RSE.DESIGNATIONID
                            from dbo.FINANCIALTRANSACTIONLINEITEM LI
                            inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.ID
                            where LI.DELETEDON is null
                        ) REVENUESPLIT on CORPORATIONREVENUE.ID = REVENUESPLIT.REVENUEID
                        left join 
                        (            
                            select
                                INSTALLMENTSPLIT.PLEDGEID,
                                INSTALLMENTSPLIT.DESIGNATIONID,
                                sum(IWO.TRANSACTIONAMOUNT) as AMOUNT
                            from dbo.INSTALLMENTSPLIT 
                            inner join dbo.INSTALLMENTSPLITWRITEOFF IWO on IWO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                            group by             
                                INSTALLMENTSPLIT.PLEDGEID, 
                                INSTALLMENTSPLIT.DESIGNATIONID            
                        ) INSTALLMENTWRITEOFF on INSTALLMENTWRITEOFF.PLEDGEID = REVENUESPLIT.REVENUEID and INSTALLMENTWRITEOFF.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID       
                        where
                            CORPORATIONREVENUE.TRANSACTIONTYPECODE in (1,3) or --Pledge or MG claim

                            (            
                                --Payment

                                CORPORATIONREVENUE.TRANSACTIONTYPECODE = 0 and 
                                (            
                                    REVENUESPLIT.APPLICATIONCODE in (0,1,3,4,5,6,18,19) or 
                                    (
                                        -- unapplied MG claim payments            

                                        REVENUESPLIT.APPLICATIONCODE = 7 and 
                                        not exists 
                                        (
                                            select 1 
                                            from dbo.INSTALLMENTSPLITPAYMENT 
                                            where REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                                        )
                                    )
                                )            
                            )
                        group by 
                            CORPORATIONREVENUE.CONSTITUENTID
                    ) as CORPORATIONGIVING on CORPORATESTRUCTURE.ID = CORPORATIONGIVING.CONSTITUENTID 
                    left join
                    (
                        select 
                            EMPLOYEEREVENUE.EMPLOYERID,
                            sum(REVENUESPLIT.AMOUNTINCURRENCY) as AMOUNT,
                            count(distinct EMPLOYEEREVENUE.ID) as [COUNT]
                        from #TMP_CORPORATESTRUCTURE_EMPLOYEEREVENUE EMPLOYEEREVENUE
                        inner join
                        (
                            select LI.ID
                                ,LI.FINANCIALTRANSACTIONID [REVENUEID]
                                ,LI.TRANSACTIONAMOUNT [AMOUNTINCURRENCY]
                                ,RSE.APPLICATIONCODE
                                ,RSE.DESIGNATIONID
                            from dbo.FINANCIALTRANSACTIONLINEITEM LI
                            inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.ID
                            where LI.DELETEDON is null
                        ) REVENUESPLIT on EMPLOYEEREVENUE.ID = REVENUESPLIT.REVENUEID
                        where
                            EMPLOYEEREVENUE.TRANSACTIONTYPECODE in (1,3) or --Pledge or MG claim

                            (            
                                --Payment

                                EMPLOYEEREVENUE.TRANSACTIONTYPECODE = 0 and 
                                (            
                                    REVENUESPLIT.APPLICATIONCODE in (0,1,3,4,5,6,18,19) or 
                                    (
                                        -- unapplied MG claim payments            

                                        REVENUESPLIT.APPLICATIONCODE = 7 and 
                                        not exists 
                                        (
                                            select 1 
                                            from dbo.INSTALLMENTSPLITPAYMENT 
                                            where REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                                        )
                                    )
                                )            
                            )
                        group by 
                            EMPLOYEEREVENUE.EMPLOYERID
                    ) as EMPLOYEEGIVING on CORPORATESTRUCTURE.ID = EMPLOYEEGIVING.EMPLOYERID
            end