USP_DESIGNATIONLEVEL_GETPLEDGEBALANCEBYPOSITIONINCURRENCY

            create procedure dbo.USP_DESIGNATIONLEVEL_GETPLEDGEBALANCEBYPOSITIONINCURRENCY
            (
                @VALUE money = null output,
                @DESIGNATIONLEVELID uniqueidentifier,
                @ORGPOSITIONSSELECTIONID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @CURRENCYID uniqueidentifier = null
            ) 
            with execute as caller
            as begin
                set @VALUE = 0;

                if @ORGPOSITIONSSELECTIONID is null
                begin
                    set @VALUE = dbo.UFN_DESIGNATIONLEVEL_GETPLEDGEBALANCEINCURRENCY(@DESIGNATIONLEVELID,@STARTDATE,@ENDDATE,@CURRENCYID);
                    return;
                end

                declare @IDS as table(ID uniqueidentifier);
                insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, null;

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

                with DESIGNATIONS_CTE as (
                    select ID 
                    from dbo.DESIGNATION D
                    where D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID or 
                          D.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID
                    )

                select @VALUE = 
                    (coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID,@CURRENCYID)), 0)
                        -
                        (
                        coalesce(( --Subtract payments of the above pledges

                            select coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYSPLIT.ID,@CURRENCYID)), 0)
                            from dbo.REVENUESPLIT PAYSPLIT
                            inner join DESIGNATIONS_CTE PAYD on PAYSPLIT.DESIGNATIONID = PAYD.ID
                            inner join dbo.REVENUE PAY on PAY.ID = PAYSPLIT.REVENUEID
                            where
                                PAY.ID IN
                                    (SELECT IP.PAYMENTID
                                        FROM DBO.INSTALLMENTPAYMENT IP
                                        inner join dbo.REVENUE PLEDGE on IP.PLEDGEID = PLEDGE.ID
                                        inner join dbo.REVENUESPLIT PLEDGESPLIT on PLEDGE.ID = PLEDGESPLIT.REVENUEID
                                        inner join DESIGNATIONS_CTE PD on PLEDGESPLIT.DESIGNATIONID = PD.ID
                                        WHERE (PLEDGE.DATE >= @STARTDATE or @STARTDATE is null) and
                                            (PLEDGE.DATE <= @ENDDATE or @ENDDATE is null))
                                 and
                                (PAY.DATE >= @STARTDATE or @STARTDATE is null) and
                                (PAY.DATE <= @ENDDATE or @ENDDATE is null)
                        ), 0)
                        +
                        coalesce(( --Subtract Writeoffs of the above pledges

                            select sum(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WOS.ID,@CURRENCYID))
                            from dbo.WRITEOFFSPLIT WOS
                            inner join DESIGNATIONS_CTE WOSD on WOS.DESIGNATIONID = WOSD.ID
                            inner join dbo.WRITEOFF WO on WOS.WRITEOFFID = WO.ID
                            inner join dbo.REVENUE R on WO.REVENUEID = R.ID
                            where 
                                (R.DATE >= @STARTDATE or @STARTDATE is null) and
                                (R.DATE <= @ENDDATE or @ENDDATE is null) and
                                R.TRANSACTIONTYPECODE in (1,3,4,6,8)
                        ), 0))
                    )
                from dbo.REVENUESPLIT RDS
                inner join DESIGNATIONS_CTE D on RDS.DESIGNATIONID = D.ID
                inner join dbo.REVENUESOLICITOR RSOL on RDS.ID = RSOL.REVENUESPLITID
                inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH 
                    on OPH.CONSTITUENTID = RSOL.CONSTITUENTID and R.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, R.DATE)
                inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
                where
                    (R.DATE >= @STARTDATE or @STARTDATE is null) and
                    (R.DATE <= @ENDDATE or @ENDDATE is null) and
                    R.TRANSACTIONTYPECODE in (1,3,4,6,8)

            end