UFN_DESIGNATION_PLEDGEDBYAPPEAL_INCURRENCY_INLINE

Creates a table of appeals, their descriptions, and the associated pledge information in a given currency
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CURRENCYID	uniqueidentifier	IN
@ORGANIZATIONCURRENCYID	uniqueidentifier	IN
@CURRENCYCODE	tinyint	IN
@DECIMALDIGITS	tinyint	IN
@ROUNDINGTYPECODE	tinyint	IN
Definition

Copy


            create function dbo.UFN_DESIGNATION_PLEDGEDBYAPPEAL_INCURRENCY_INLINE
            (
                @STARTDATE datetime,  -- Expects EARLIESTTIME

                @ENDDATE datetime, -- Expects LATESTTIME

                @CURRENCYID uniqueidentifier = null,
                @ORGANIZATIONCURRENCYID uniqueidentifier,
                @CURRENCYCODE tinyint,
                @DECIMALDIGITS tinyint,
                @ROUNDINGTYPECODE tinyint
            )
            returns table
            as
            return
            (                    
                    select ARS.APPEALID APPEALID, ARS.DESIGNATIONID DESID, ARS.DESIGNATIONNAME DESNAME, 
                        coalesce(sum(ARS.REVENUESPLITAMOUNTINCURRENCY),0) DESTOTALPLEDGED,
                        (select coalesce(sum(APPEALREVENUESPLIT.REVENUESPLITAMOUNTINCURRENCY), 0) 
                            from 
                                dbo.UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) APPEALREVENUESPLIT
                            where 
                                APPEALREVENUESPLIT.REVENUESPLITID in (
                                    select ip.PAYMENTID
                                        from dbo.INSTALLMENTPAYMENT ip
                                            inner join dbo.REVENUE pledgerev on ip.PLEDGEID = pledgerev.ID
                                            inner join dbo.REVENUESPLIT pledgerevspl on pledgerev.ID = pledgerevspl.REVENUEID
                                        where (pledgerev.DATE >= @STARTDATE or @STARTDATE is null) and
                                            (pledgerev.DATE <= @ENDDATE or @ENDDATE is null) and 
                                            pledgerev.APPEALID=ARS.APPEALID and pledgerevspl.DESIGNATIONID=ARS.DESIGNATIONID) and
                                (APPEALREVENUESPLIT.APPEALID=ARS.APPEALID and APPEALREVENUESPLIT.DESIGNATIONID=ARS.DESIGNATIONID) and 
                                (APPEALREVENUESPLIT.DATE >= @STARTDATE or @STARTDATE is null) and
                                (APPEALREVENUESPLIT.DATE <= @ENDDATE or @ENDDATE is null)) DESPLEDGESPAID,
                        (select coalesce(sum(wos.WRITEOFFSPLITAMOUNTINCURRENCY),0) 
                            from dbo.UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) wos
                            where
                                (wos.DATE >= @STARTDATE or @STARTDATE is null) and
                                (wos.DATE <= @ENDDATE or @ENDDATE is null) and
                                (wos.APPEALID=ARS.APPEALID and wos.DESIGNATIONID=ARS.DESIGNATIONID))DESPLEDGESWRITTENOFF
                        from dbo.UFN_APPEALREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) ARS
                        where (ARS.APPEALID is not null) and 
                            (ARS.TRANSACTIONTYPECODE in (1,3,4,8)) and
                            ((ARS.DATE >= @STARTDATE) or @STARTDATE is null) and
                            ((ARS.DATE <= @ENDDATE) or @ENDDATE is null)
                        group by 
                            ARS.APPEALID, 
                            ARS.DESIGNATIONID, 
                            ARS.DESIGNATIONNAME
            )