UFN_CORPORATION_GETEMPLOYEEWRITEOFFTOTAL_BULK

create function dbo.UFN_CORPORATION_GETEMPLOYEEWRITEOFFTOTAL_BULK
(
    @CURRENTAPPUSERID uniqueidentifier
    ,@ROOTID uniqueidentifier
)
returns table
as
    return(
        select
            CONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID,
            AMOUNTINCURRENCY = sum(WRITEOFFSPLIT.TRANSACTIONAMOUNT),
            [DATE] = WRITEOFF.CALCULATEDDATE
        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.FINANCIALTRANSACTION WRITEOFF on WRITEOFF.PARENTID = REVENUE.ID and WRITEOFF.TYPECODE = 20
        inner join dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFSPLIT on WRITEOFFSPLIT.FINANCIALTRANSACTIONID = WRITEOFF.ID
        inner join dbo.REVENUESPLIT_EXT on WRITEOFFSPLIT.ID = REVENUESPLIT_EXT.ID
        inner join dbo.CORPORATESTRUCTURESELECTION SELECTION
            on SELECTION.SELECTEDID = RELATIONSHIP.RECIPROCALCONSTITUENTID  and SELECTION.ROOTID = @ROOTID and SELECTION.APPUSERID = @CURRENTAPPUSERID            

        where
            REVENUE.TYPECODE in (1,3) and --PLEDGE and MATCHINGGIFT

            REVENUE.DELETEDON is null and
            WRITEOFFSPLIT.DELETEDON is null and
            WRITEOFFSPLIT.TYPECODE != 1 and
            WRITEOFF.DELETEDON is null
        group by RELATIONSHIP.RECIPROCALCONSTITUENTID,WRITEOFF.CALCULATEDDATE
    )