USP_DESIGNATION_NEWCOMMITMENTTOTALINCURRENCY_BYPOSITION

        create procedure dbo.USP_DESIGNATION_NEWCOMMITMENTTOTALINCURRENCY_BYPOSITION
        (
            @NEWCOMMITMENTTOTAL money = null output,
            @STARTDATE datetime = null, 
            @ENDDATE datetime = null, 
            @CURRENCYID uniqueidentifier = null,
            @DESIGNATIONID uniqueidentifier = null,
            @ORGPOSITIONSSELECTIONID uniqueidentifier = null
        )
        as begin
            declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
            declare @DECIMALDIGITS tinyint;
            declare @ROUNDINGTYPECODE tinyint;

            set @NEWCOMMITMENTTOTAL = 0;

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

            if @CURRENCYID is null
                set @CURRENCYID = @ORGANIZATIONCURRENCYID;

            select
                @DECIMALDIGITS = DECIMALDIGITS,
                @ROUNDINGTYPECODE = ROUNDINGTYPECODE
            from
                dbo.UFN_CURRENCY_GETPROPERTIES(@CURRENCYID)

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

            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 @NEWCOMMITMENTTOTAL =
                coalesce(sum(SPLIT.AMOUNTINCURRENCY),0)
                -
                (
                    select 
                        coalesce(sum(WRITEOFFSPLIT.AMOUNTINCURRENCY), 0) 
                    from 
                        dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) WRITEOFFSPLIT
                        inner join dbo.DESIGNATION WRITEOFFDESIGNATION on WRITEOFFSPLIT.DESIGNATIONID = WRITEOFFDESIGNATION.ID
                        inner join DESIGNATIONS_CTE D on WRITEOFFDESIGNATION.ID = D.ID
                        inner join dbo.WRITEOFF WRITEOFF on WRITEOFFSPLIT.WRITEOFFID = WRITEOFF.ID
                        inner join dbo.FINANCIALTRANSACTION WRITEOFFREVENUE on WRITEOFF.REVENUEID = WRITEOFFREVENUE.ID
                    where 
                        (WRITEOFFREVENUE.DATE >= @STARTDATE or @STARTDATE is null) and
                        (WRITEOFFREVENUE.DATE <= @ENDDATE or @ENDDATE is null) and
                        (WRITEOFFREVENUE.TYPECODE in (1,6))
                )
            from 
                dbo.DESIGNATION D
                inner join DESIGNATIONS_CTE D2 on D.ID = D2.ID
                inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) SPLIT on D2.ID = SPLIT.DESIGNATIONID
                inner join dbo.FINANCIALTRANSACTION REVENUE on SPLIT.REVENUEID = REVENUE.ID
                inner join dbo.REVENUESOLICITOR RSOL on SPLIT.ID = RSOL.REVENUESPLITID
                inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH 
                    on OPH.CONSTITUENTID = RSOL.CONSTITUENTID and REVENUE.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, REVENUE.DATE)
                inner join @IDS as SELECTION on OPH.ID = SELECTION.ID
            where 
                (REVENUE.DATE >= @STARTDATE or @STARTDATE is null) and
                (REVENUE.DATE <= @ENDDATE or @ENDDATE is null) and
                (REVENUE.TYPECODE in (1,6))        
        end