UFN_CORPORATESTRUCTURE_GETEMPLOYEEGIVINGTOTAL

Returns the employees for the given corporate structure based on the currently selected corporations and corporate relationship types.
Return

Return Type
money
Parameters

Parameter	Parameter Type	Mode
@ROOTID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
Definition

Copy


            CREATE function [dbo].[UFN_CORPORATESTRUCTURE_GETEMPLOYEEGIVINGTOTAL]
            (
                @ROOTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null 
            )
            returns money
            as
                begin
                declare @d money;    

                select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                    @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

                select
                    @d = sum(BASEAMOUNT)
                from
                (
                    select 
                        REVENUESPLIT.BASEAMOUNT
                    from
                        dbo.RELATIONSHIP 
                    inner join 
                        dbo.EMPLOYEECORPORATERELATIONSHIPTYPE on RELATIONSHIP.RELATIONSHIPTYPECODEID = EMPLOYEECORPORATERELATIONSHIPTYPE.RELATIONSHIPTYPECODEID
                    inner join 
                        dbo.CORPORATESTRUCTURESELECTION as CSS on CSS.SELECTEDID = RELATIONSHIP.RECIPROCALCONSTITUENTID
                    inner join 
                        dbo.FINANCIALTRANSACTION REVENUE on REVENUE.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                    inner join 
                        (select distinct REVENUEID from dbo.UFN_SITEACCESSABLE_REVENUESPLITID_FORUSER(@CURRENTAPPUSERID)) SITEFILTER on SITEFILTER.REVENUEID = REVENUE.ID
                    inner join 
                        dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
                    inner join
                        dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                    left join 
                        dbo.INSTALLMENTSPLITPAYMENT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                    where
                        CSS.ROOTID = @ROOTID
                        and CSS.APPUSERID = @CURRENTAPPUSERID
                        and REVENUE.DELETEDON is null
                        and REVENUESPLIT.DELETEDON is null
                        and REVENUESPLIT.TYPECODE <> 1
                        and (@STARTDATE is null or cast(REVENUE.DATE as datetime) >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))
                        and    (@ENDDATE is null or cast(REVENUE.DATE as datetime) <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
                        and (REVENUE.TYPECODE in (1,3) or --Pledge or MG claim

                                (REVENUE.TYPECODE = 0 and 
                                    (REVENUESPLIT_EXT.APPLICATIONCODE in (0,1,3,4,5,6,18,19) or 
                                        (REVENUESPLIT_EXT.APPLICATIONCODE = 7 and INSTALLMENTSPLITPAYMENT.ID is null) -- unapplied MG claim payments

                                    )
                                )
                            ) --Payments

                    group by REVENUESPLIT.ID, REVENUESPLIT.BASEAMOUNT
                ) as DISTINCTREVENUE

                return @d;
                end