UFN_CORPORATION_GETEMPLOYEERECOGNITIONCOUNT

Returns the number of transactions an employee has recognition credits for based on the current corporate relationship types.
Return

Return Type
int
Parameters

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

Copy


            CREATE function [dbo].[UFN_CORPORATION_GETEMPLOYEERECOGNITIONCOUNT]
            (
                @CONSTITUENTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null 
            )
            returns int
            as
                begin
                declare @count int

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

                select @count = count(distinct REVENUE.ID)
                from
                    dbo.RELATIONSHIP 
                        inner join dbo.EMPLOYEECORPORATERELATIONSHIPTYPE on RELATIONSHIP.RELATIONSHIPTYPECODEID = EMPLOYEECORPORATERELATIONSHIPTYPE.RELATIONSHIPTYPECODEID
                        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
                        inner join dbo.REVENUERECOGNITION on REVENUERECOGNITION.REVENUESPLITID = REVENUESPLIT.ID
                        left join dbo.INSTALLMENTSPLITPAYMENT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                where
                    REVENUE.DELETEDON is null and
                    REVENUESPLIT.DELETEDON is null and
                    REVENUESPLIT.TYPECODE <> 1 and
                    RELATIONSHIP.RECIPROCALCONSTITUENTID = @CONSTITUENTID and
                    (@STARTDATE is null or REVENUERECOGNITION.EFFECTIVEDATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)) and
                    (@ENDDATE is null or REVENUERECOGNITION.EFFECTIVEDATE <= 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


                return @count;
                end