USP_KPI_REVENUE_APPEALAMOUNTTOTAL

        CREATE procedure dbo.USP_KPI_REVENUE_APPEALAMOUNTTOTAL 
        @VALUE money output,
        @DESIGNATIONID uniqueidentifier = null,
        @APPEALID uniqueidentifier,
        @ASOFDATE datetime,
        @SELECTIONID uniqueidentifier = null,
            @CURRENCYID uniqueidentifier = 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;


        --if the designation id is null, this procedure returns the revenue records that apply to an appeal

        --else it joins the revenue split table with a table(DESIGNATIONS_CTE) that contains the designation ids

        --for the input designation and its children designations


        if @SELECTIONID is null
          begin
            if @DESIGNATIONID is null
              begin
                select @RECEIVED = 
                  coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID,@CURRENCYID)), 0)
                  from dbo.REVENUESPLIT RDS
                  inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                  where
                    (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                    (R.APPEALID = @APPEALID) and
                     R.TRANSACTIONTYPECODE = 0; 
              end
            else
              begin
                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(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID,@CURRENCYID)), 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
                  where
                    (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                    (R.APPEALID = @APPEALID) and
                     R.TRANSACTIONTYPECODE = 0; 
              end

            if @DESIGNATIONID is null
              begin
                select @PLEDGEBALANCE =
                  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 R on IP.PLEDGEID = R.ID
                          inner join dbo.REVENUESPLIT RS on R.ID = RS.REVENUEID
                          WHERE (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                            (R.APPEALID = @APPEALID) and
                            (PAY.DATE <= @ASOFDATE or @ASOFDATE 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 dbo.WRITEOFF WO on WOS.WRITEOFFID = WO.ID
                      inner join dbo.REVENUE R on WO.REVENUEID = R.ID
                      where 
                        (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                        (R.APPEALID = @APPEALID) and
                         R.TRANSACTIONTYPECODE in (1)
                  ), 0))

                from dbo.REVENUESPLIT RDS
                inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                where 
                  (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                  (R.APPEALID = @APPEALID) and
                   R.TRANSACTIONTYPECODE in (1);
              end
            else
              begin
                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(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 R on IP.PLEDGEID = R.ID
                          inner join dbo.REVENUESPLIT RS on R.ID = RS.REVENUEID
                          inner join DESIGNATIONS_CTE RD on RS.DESIGNATIONID = RD.ID
                          WHERE (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                            (R.APPEALID = @APPEALID) and
                            (PAY.DATE <= @ASOFDATE or @ASOFDATE 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 <= @ASOFDATE or @ASOFDATE is null) and
                        (R.APPEALID = @APPEALID) and
                         R.TRANSACTIONTYPECODE in (1)
                  ), 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
                  where 
                    (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                    (R.APPEALID = @APPEALID) and
                     R.TRANSACTIONTYPECODE in (1);
              end
          end 
        else
          begin
            if @DESIGNATIONID is null
              begin
                select @RECEIVED = 
                  coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID,@CURRENCYID)), 0)
            from dbo.REVENUESPLIT RDS
                  inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                  inner join UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as IDSET_36515C01F49C49F785B773BCB3C6A6C7 on IDSET_36515C01F49C49F785B773BCB3C6A6C7.ID = RDS.REVENUEID
                  where
                    (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                    (R.APPEALID = @APPEALID) and
                    R.TRANSACTIONTYPECODE = 0; 
              end
            else
              begin
                  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(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID,@CURRENCYID)), 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 UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as IDSET_36515C01F49C49F785B773BCB3C6A6C7 on IDSET_36515C01F49C49F785B773BCB3C6A6C7.ID = RDS.REVENUEID
                  where
                    (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                    (R.APPEALID = @APPEALID) and
                    R.TRANSACTIONTYPECODE = 0; 
              end;
            if @DESIGNATIONID is null
              begin
                select @PLEDGEBALANCE =
                  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
                      inner join UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as IDSET_36515C01F49C49F785B773BCB3C6A6C7 on IDSET_36515C01F49C49F785B773BCB3C6A6C7.ID = PAYSPLIT.REVENUEID
                      where 
                        PAY.ID IN
                        (SELECT IP.PAYMENTID
                          FROM dbo.INSTALLMENTPAYMENT IP
                          inner join dbo.REVENUE R on IP.PLEDGEID = R.ID
                          inner join dbo.REVENUESPLIT RS on R.ID = RS.REVENUEID
                          inner join UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as IDSET_36515C01F49C49F785B773BCB3C6A6C7 on IDSET_36515C01F49C49F785B773BCB3C6A6C7.ID = RS.REVENUEID
                          WHERE (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                            (R.APPEALID = @APPEALID) and
                            (PAY.DATE <= @ASOFDATE or @ASOFDATE 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 dbo.WRITEOFF WO on WOS.WRITEOFFID = WO.ID
                      inner join dbo.REVENUE R on WO.REVENUEID = R.ID
                      inner join UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as IDSET_36515C01F49C49F785B773BCB3C6A6C7 on IDSET_36515C01F49C49F785B773BCB3C6A6C7.ID = R.ID
                      where 
                        (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                        (R.APPEALID = @APPEALID) and
                        R.TRANSACTIONTYPECODE in (1)
                  ), 0)) 

                  from dbo.REVENUESPLIT RDS
                  inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
                  inner join UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as IDSET_36515C01F49C49F785B773BCB3C6A6C7 on IDSET_36515C01F49C49F785B773BCB3C6A6C7.ID = RDS.REVENUEID
                  where 
                    (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                    (R.APPEALID = @APPEALID) and
                    R.TRANSACTIONTYPECODE in (1);
              end
            else
              begin
                          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(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
                      inner join UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as IDSET_36515C01F49C49F785B773BCB3C6A6C7 on IDSET_36515C01F49C49F785B773BCB3C6A6C7.ID = PAYSPLIT.REVENUEID
                      where 
                        PAY.ID IN
                        (SELECT IP.PAYMENTID
                          FROM dbo.INSTALLMENTPAYMENT IP
                          inner join dbo.REVENUE R on IP.PLEDGEID = R.ID
                          inner join dbo.REVENUESPLIT RS on R.ID = RS.REVENUEID
                          inner join UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as IDSET_36515C01F49C49F785B773BCB3C6A6C7 on IDSET_36515C01F49C49F785B773BCB3C6A6C7.ID = RS.REVENUEID
                          inner join DESIGNATIONS_CTE RD on RS.DESIGNATIONID = RD.ID
                          WHERE (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                            (R.APPEALID = @APPEALID) and
                            (PAY.DATE <= @ASOFDATE or @ASOFDATE 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
                      inner join UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as IDSET_36515C01F49C49F785B773BCB3C6A6C7 on IDSET_36515C01F49C49F785B773BCB3C6A6C7.ID = R.ID
                      where 
                        (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                        (R.APPEALID = @APPEALID) and
                        R.TRANSACTIONTYPECODE in (1)
                  ), 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 UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) as IDSET_36515C01F49C49F785B773BCB3C6A6C7 on IDSET_36515C01F49C49F785B773BCB3C6A6C7.ID = RDS.REVENUEID
                  where 
                    (R.DATE <= @ASOFDATE or @ASOFDATE is null) and
                    (R.APPEALID = @APPEALID) and
                    R.TRANSACTIONTYPECODE in (1);
              end
        end
set @VALUE = @RECEIVED + @PLEDGEBALANCE;