UFN_DESIGNATION_GETTOTALRECEIVEDBYAPPEAL

Returns the total amount received for a given designation filtered by appeal information.
Return

Return Type
money
Parameters

Parameter	Parameter Type	Mode
@DESIGNATIONID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@APPEALID	uniqueidentifier	IN
@APPEALREPORTCODEID	uniqueidentifier	IN
@BUSINESSUNITCODEID	uniqueidentifier	IN
Definition

Copy


            CREATE function dbo.UFN_DESIGNATION_GETTOTALRECEIVEDBYAPPEAL
            (
                @DESIGNATIONID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @APPEALID uniqueidentifier = null,
                @APPEALREPORTCODEID uniqueidentifier = null,
                @BUSINESSUNITCODEID uniqueidentifier = null
            ) 
            returns money
            with execute as caller
            as begin
                declare @RESULT money;

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

                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 @RESULT = 
                    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
          left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID = A.ID
                    where
                        (R.DATE >= @STARTDATE or @STARTDATE is null) and
                        (R.DATE <= @ENDDATE or @ENDDATE is null) and
                        (A.ID = @APPEALID or @APPEALID is null) and
                        (A.APPEALREPORT1CODEID = @APPEALREPORTCODEID or @APPEALREPORTCODEID is null) and
                        (ABU.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
                        R.TRANSACTIONTYPECODE = 0;

                return @RESULT;
            end