UFN_CONSTITUENT_GETGROUPREVENUE

This function returns all the revenue for a group and members.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@GROUPINCLUDESMEMBERGIVING	bit	IN
@CURRENTAPPUSERID	uniqueidentifier	IN

Definition

Copy



        CREATE function dbo.UFN_CONSTITUENT_GETGROUPREVENUE
        (
            @CONSTITUENTID uniqueidentifier, 
            @GROUPINCLUDESMEMBERGIVING bit,
            @CURRENTAPPUSERID uniqueidentifier = null
        )
        returns table
        as return
            select
                R.ID, cast(R.DATE as datetime) DATE, R.BASEAMOUNT AMOUNT, R.TYPECODE TRANSACTIONTYPECODE
            from
                dbo.FINANCIALTRANSACTION R with (nolock)
            where 
                R.CONSTITUENTID = @CONSTITUENTID
                and R.DELETEDON is null
                and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1

            union all
            select
                R.ID, cast(R.DATE as datetime) DATE, R.BASEAMOUNT AMOUNT, R.TYPECODE TRANSACTIONTYPECODE
            from
                dbo.FINANCIALTRANSACTION R with (nolock)
                inner join dbo.GROUPMEMBER GM with (nolock) on R.CONSTITUENTID = GM.MEMBERID
                inner join dbo.GROUPMEMBERDATERANGE GMDR with (nolock) on GMDR.GROUPMEMBERID = GM.ID
            where 
                GM.GROUPID = @CONSTITUENTID
                and R.DELETEDON is null
                and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= R.DATE))
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= R.DATE)) 
                    or (GMDR.DATEFROM <= R.DATE and GMDR.DATETO >= R.DATE))
                and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1