USP_KPI_DESIGNATION_REVENUETOTAL_INTERNAL

            CREATE procedure dbo.USP_KPI_DESIGNATION_REVENUETOTAL_INTERNAL

            @ASOFDATE datetime,
            @TOTAL money = 0 output,
            @DESIGNATIONID uniqueidentifier,
            @APPEALID uniqueidentifier = null,
            @BUSINESSUNITCODEID uniqueidentifier = null,
            @APPEALREPORTCODE1ID uniqueidentifier = null,
            @STARTDATE datetime = null

            as    

            set nocount on;

            declare @RECEIVED money;
            declare @PLEDGEBALANCE money;

            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 @RECEIVED = 
                    coalesce(sum(RDS.AMOUNT), 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
                    left join dbo.APPEAL A on R.APPEALID = A.ID
                    where
                        (R.DATE >= @STARTDATE or @STARTDATE is null) and
                        (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                        (A.ID = @APPEALID or @APPEALID is null) and
                        (A.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and
                        (A.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
                        R.TRANSACTIONTYPECODE = 0;                

                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 @PLEDGEBALANCE =
                    coalesce(sum(RDS.AMOUNT), 0)
                    -
                    (
                    coalesce(( --Subtract payments of the above pledges

                        select coalesce(sum(PAYSPLIT.AMOUNT), 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
                        left join dbo.APPEAL PAYAPP on PAY.APPEALID = PAYAPP.ID                        
                        where
                            PAY.ID IN
                                (SELECT IP.PAYMENTID
                                    FROM dbo.INSTALLMENTPAYMENT IP
                                    inner join dbo.REVENUE R on IP.PLEDGEID = R.ID
                                    left join dbo.APPEAL A on R.APPEALID = A.ID
                                    inner join dbo.REVENUESPLIT RS on R.ID = RS.REVENUEID
                                    inner join DESIGNATIONS_CTE RD on RS.DESIGNATIONID = RD.ID
                                    WHERE (R.DATE >= @STARTDATE or @STARTDATE is null) and
                                    (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                                    (A.ID = @APPEALID or @APPEALID is null) and
                                    (A.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and 
                                    (A.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)) and
                            (PAY.DATE >= @STARTDATE or @STARTDATE is null) and
                            (PAY.DATE <= @ASOFDATE or @ASOFDATE is null) and
                            (PAYAPP.ID = @APPEALID or @APPEALID is null) and
                            (PAYAPP.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and 
                            (PAYAPP.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)
                    ), 0)
                    +
                    coalesce(( --Subtract Writeoffs of the above pledges

                        select sum(WOS.AMOUNT)
                        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
                        left join dbo.APPEAL A on R.APPEALID = A.ID        

                        where 
                            (R.DATE >= @STARTDATE or @STARTDATE is null) and
                            (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                            (A.ID = @APPEALID or @APPEALID is null) and
                            (A.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and 
                            (A.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
                            R.TRANSACTIONTYPECODE in (1,3)
                    ), 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
                left join dbo.APPEAL A on R.APPEALID = A.ID
                where
                    (R.DATE >= @STARTDATE or @STARTDATE is null) and
                    (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                    (A.ID = @APPEALID or @APPEALID is null) and
                    (A.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and 
                    (A.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
                    R.TRANSACTIONTYPECODE in (1,3);

                set @TOTAL = @RECEIVED + @PLEDGEBALANCE;