USP_DATAFORMTEMPLATE_VIEW_REVENUEBATCHHOUSEHOLDINFO

The load procedure used by the view dataform template "Revenue Batch Household Information View Form"
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@ISHOUSEHOLD	bit	INOUT	Is household
@NAME	nvarchar(700)	INOUT	Name
@CURRENTMEMBERCOUNT	int	INOUT	Current member count
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REVENUEBATCHHOUSEHOLDINFO
                (
                    @ID uniqueidentifier,
                    @ISHOUSEHOLD bit = null output,
                    @NAME nvarchar(700) = null output,
                    @CURRENTMEMBERCOUNT int = null output,
                    @DATALOADED bit = 0 output,
                    @CURRENTAPPUSERID uniqueidentifier
                ) as
                begin
                    set nocount on;

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());                                                       

                    if exists(select top(1) ID from dbo.CONSTITUENT where CONSTITUENT.ID = @ID)
                    begin

                        -- Check constituent security.  The check is done manually since it only applies to existing constituents.

                        if (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1) or
                           ((dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, '39eead9c-6cb2-4a76-92b0-ed4f8156d26b', @ID) = 1) and -- Constituent group security

                           exists (select 1 from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@ID) where dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, '39eead9c-6cb2-4a76-92b0-ed4f8156d26b', SITEID) = 1)) -- Site security

                        begin

                            select
                                @DATALOADED = 1,
                                @NAME = CONSTITUENT_NF.NAME,
                                @ISHOUSEHOLD = case when GROUPDATA.GROUPTYPECODE = 0 then 1 else 0 end
                            from 
                                dbo.CONSTITUENT
                            left join 
                                dbo.GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID
                            outer apply
                                dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF
                            where 
                                CONSTITUENT.ID = @ID

                            select 
                                @CURRENTMEMBERCOUNT = count(GROUPMEMBER.ID) 
                            from 
                                dbo.GROUPMEMBER
                            left outer join 
                                dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                            where ((GROUPMEMBERDATERANGE.DATEFROM is null and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @CURRENTDATE))
                                or (GROUPMEMBERDATERANGE.DATETO is null and (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATE)) 
                                or (GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATE and GROUPMEMBERDATERANGE.DATETO > @CURRENTDATE))
                            and 
                                GROUPMEMBER.GROUPID = @ID;

                        end
                        else
                        begin
                            raiserror('BBERR_DB_RECORDSECURITY_PERMISSION_DENIED', 13, 1)
                        end
                    end

                    if @DATALOADED = 0
                        if exists(select top(1) ID from dbo.BATCHREVENUECONSTITUENT where BATCHREVENUECONSTITUENT.ID = @ID)
                        begin

                            select
                                @DATALOADED = 1,
                                @NAME = BATCHREVENUECONSTITUENT.NAME,
                                @ISHOUSEHOLD = case 
                                    when (BATCHREVENUECONSTITUENT.GROUPTYPECODE = 0 and BATCHREVENUECONSTITUENT.ISGROUP = 1) then 1 
                                    else 0 
                                end
                            from 
                                dbo.BATCHREVENUECONSTITUENT
                            where 
                                BATCHREVENUECONSTITUENT.ID = @ID

                            select 
                                @CURRENTMEMBERCOUNT = count(BATCHREVENUECONSTITUENTGROUPMEMBER.ID) 
                            from 
                                dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
                            left outer join 
                                dbo.GROUPMEMBERDATERANGE on BATCHREVENUECONSTITUENTGROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                            where
                                BATCHREVENUECONSTITUENTGROUPMEMBER.GROUPID = @ID;

                        end

                    return 0;
                end