UFN_CORPORATESTRUCTURE_GETEMPLOYEEGIVINGTOTALINCURRENCY

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

Return Type
money
Parameters

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

Copy


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

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

                select
                    @d = sum(AMOUNT)
                from
                (
                    select 
                        dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID,@CURRENCYID) as AMOUNT
                    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 
                        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
                    cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVENUESITE
                    where
                        CSS.ROOTID = @ROOTID
                        and REVENUE.DELETEDON is null
                        and REVENUESPLIT.DELETEDON is null
                        and REVENUESPLIT.TYPECODE <> 1                        
                        and CSS.APPUSERID = @CURRENTAPPUSERID
                        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

                        and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, REVENUESITE.SITEID) = 1
                    group by REVENUESPLIT.ID, REVENUESPLIT.BASEAMOUNT
                ) as DISTINCTREVENUE

                    return @d;
                end