UFN_CORPORATION_GETEMPLOYEERECOGNITIONTOTALINCURRENCY_BULK

            CREATE function [dbo].[UFN_CORPORATION_GETEMPLOYEERECOGNITIONTOTALINCURRENCY_BULK]
            (
                @CURRENTAPPUSERID uniqueidentifier = null,
                @CURRENCYID uniqueidentifier = null,
                @ORGANIZATIONCURRENCYID uniqueidentifier,
                @DECIMALDIGITS tinyint,
                @ROUNDINGTYPECODE tinyint
            )
      returns table
      as
      return(
        with RECOGNITION (ID, CONSTITUENTID, AMOUNTINCURRENCY, EFFECTIVEDATE, APPLICATIONCODE, TRANSACTIONTYPECODE, REVENUECONSTITUENTID, DATE, REVENUEID, REVENUESPLITID, REVENUESPLITTYPECODE)
        as 
        (
            select RR.ID, RR.CONSTITUENTID, RR.AMOUNTINCURRENCY, RR.EFFECTIVEDATE, RR.APPLICATIONCODE, RR.TRANSACTIONTYPECODE, RR.REVENUECONSTITUENTID, RR.DATE, RR.REVENUEID, RR.REVENUESPLITID, RR.REVENUESPLITTYPECODE
            from dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR

            union all

            select RC.ID, RC.CONSTITUENTID, RC.AMOUNTINCURRENCY, RC.EFFECTIVEDATE, RC.APPLICATIONCODE, RC.TRANSACTIONTYPECODE, RC.REVENUECONSTITUENTID, RC.DATE, RC.REVENUEID, RC.REVENUESPLITID, RC.REVENUESPLITTYPECODE
            from dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RC
        )
        select
            RELATIONSHIP.RECIPROCALCONSTITUENTID as CONSTITUENTID,
            coalesce(sum(RECOGNITION.AMOUNTINCURRENCY), 0) as [AMOUNTINCURRENCY],
            RECOGNITION.EFFECTIVEDATE
        from
            dbo.RELATIONSHIP 
            inner join dbo.EMPLOYEECORPORATERELATIONSHIPTYPE on RELATIONSHIP.RELATIONSHIPTYPECODEID = EMPLOYEECORPORATERELATIONSHIPTYPE.RELATIONSHIPTYPECODEID
            inner join RECOGNITION on RECOGNITION.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
            inner join (select distinct REVENUEID from dbo.UFN_SITEACCESSABLE_REVENUESPLITID_FORUSER(@CURRENTAPPUSERID)) SITEFILTER on SITEFILTER.REVENUEID = RECOGNITION.REVENUEID
            left join dbo.INSTALLMENTSPLITPAYMENT on RECOGNITION.REVENUESPLITID = INSTALLMENTSPLITPAYMENT.PAYMENTID
        where
            (RECOGNITION.TRANSACTIONTYPECODE in (1,3,8) or --Pledge, MG claim, Donor Challenge Claim

                (RECOGNITION.TRANSACTIONTYPECODE = 0 and 
                    (RECOGNITION.APPLICATIONCODE in (0,1,3,4,5,6,8) or 
                        (RECOGNITION.APPLICATIONCODE = 7 and INSTALLMENTSPLITPAYMENT.ID is null) -- unapplied MG claim payments

                    )
                )
            ) --Payments

        group by RELATIONSHIP.RECIPROCALCONSTITUENTID, RECOGNITION.EFFECTIVEDATE
    );