USP_DESIGNATION_GETPLEDGEBALANCEBYPOSITION

            CREATE procedure dbo.USP_DESIGNATION_GETPLEDGEBALANCEBYPOSITION
            (
                @VALUE money = null output,
                @DESIGNATIONID uniqueidentifier,
                @ORGPOSITIONSSELECTIONID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @ROLLUPTOTAL bit = 0,
                @CURRENCYID uniqueidentifier = null
            ) 
            as begin
                set @VALUE = 0;

                if @CURRENCYID is null
                    set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()

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

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

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

                if @ROLLUPTOTAL = 0
                    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 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
                                            WHERE (PLEDGE.DATE >= @STARTDATE or @STARTDATE is null) and
                                                (PLEDGE.DATE <= @ENDDATE or @ENDDATE is null) and
                                                (PLEDGESPLIT.DESIGNATIONID = @DESIGNATIONID))
                                     and
                                    (PAY.DATE >= @STARTDATE or @STARTDATE is null) and
                                    (PAY.DATE <= @ENDDATE or @ENDDATE is null) and 
                                    PAYSPLIT.DESIGNATIONID = @DESIGNATIONID
                            ), 0)
                            +
                            coalesce(( --Subtract Writeoffs of the above pledges

                                select sum(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WOS.ID, @CURRENCYID))
                                from dbo.WRITEOFFSPLIT WOS
                                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
                                    WOS.DESIGNATIONID = @DESIGNATIONID and
                                    R.TRANSACTIONTYPECODE in (1,3,4,6,8)
                            ), 0)) 
                        )
                    from dbo.REVENUESPLIT RDS
                    inner join dbo.REVENUE R on R.ID = RDS.REVENUEID                    
                    inner join dbo.REVENUESOLICITOR RSOL on RDS.ID = RSOL.REVENUESPLITID
                    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
                        RDS.DESIGNATIONID = @DESIGNATIONID and
                        R.TRANSACTIONTYPECODE in (1,3,4,6,8)
                else
                    begin
                    declare @DL1ID uniqueidentifier;
                    declare @DL2ID uniqueidentifier;
                    declare @DL3ID uniqueidentifier;
                    declare @DL4ID uniqueidentifier;
                    declare @DL5ID uniqueidentifier;

                    select @DL1ID = DESIGNATIONLEVEL1ID,
                            @DL2ID = DESIGNATIONLEVEL2ID,
                            @DL3ID = DESIGNATIONLEVEL3ID,
                            @DL4ID = DESIGNATIONLEVEL4ID,
                            @DL5ID = DESIGNATIONLEVEL5ID
                    from dbo.DESIGNATION
                    where ID = @DESIGNATIONID;

                    with DESIGNATIONS_CTE as (
                        select ID 
                        from dbo.DESIGNATION D
                        where D.DESIGNATIONLEVEL1ID = @DL1ID and
                              (D.DESIGNATIONLEVEL2ID = @DL2ID or @DL2ID is null) and 
                              (D.DESIGNATIONLEVEL3ID = @DL3ID or @DL3ID is null) and 
                              (D.DESIGNATIONLEVEL4ID = @DL4ID or @DL4ID is null) and 
                              (D.DESIGNATIONLEVEL5ID = @DL5ID or @DL5ID is null)
                        )

                    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.REVENUE R on R.ID = RDS.REVENUEID                    
                    inner join dbo.REVENUESOLICITOR RSOL on RDS.ID = RSOL.REVENUESPLITID
                    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

            end