USP_BATCHREVENUE_ADDHOUSEHOLD

Adds a new revenue batch household.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@NAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(300)	IN
@GIVESANONYMOUSLY	bit	IN
@ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	IN
@ADDRESS_COUNTRYID	uniqueidentifier	IN
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN
@ADDRESS_CITY	nvarchar(50)	IN
@ADDRESS_STATEID	uniqueidentifier	IN
@ADDRESS_POSTCODE	nvarchar(12)	IN
@PHONE_PHONETYPECODEID	uniqueidentifier	IN
@PHONE_NUMBER	nvarchar(100)	IN
@EMAIL_EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@EMAIL_EMAILADDRESS	UDT_EMAILADDRESS	IN
@WEBADDRESS	UDT_WEBADDRESS	IN
@PRIMARYCONTACTID	uniqueidentifier	IN
@PRIMARYCONTACT_KEYNAME	nvarchar(100)	IN
@PRIMARYCONTACT_FIRSTNAME	nvarchar(50)	IN
@PRIMARYCONTACT_MIDDLENAME	nvarchar(50)	IN
@PRIMARYCONTACT_TITLECODEID	uniqueidentifier	IN
@PRIMARYCONTACT_SUFFIXCODEID	uniqueidentifier	IN
@PRIMARYCONTACT_COPYHOUSEHOLDCONTACT	bit	IN
@SECONDMEMBERID	uniqueidentifier	IN
@SECONDMEMBER_KEYNAME	nvarchar(100)	IN
@SECONDMEMBER_FIRSTNAME	nvarchar(50)	IN
@SECONDMEMBER_MIDDLENAME	nvarchar(50)	IN
@SECONDMEMBER_TITLECODEID	uniqueidentifier	IN
@SECONDMEMBER_SUFFIXCODEID	uniqueidentifier	IN
@SECONDMEMBER_COPYHOUSEHOLDCONTACT	bit	IN
@SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID	uniqueidentifier	IN
@SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID	uniqueidentifier	IN
@SECONDMEMBER_RELATIONSHIP_STARTDATE	datetime	IN
@SECONDMEMBER_RELATIONSHIP_ISSPOUSE	bit	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@ADDRESS_DONOTMAIL	bit	IN
@ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	IN
@OMITFROMVALIDATION	bit	IN
@CART	nvarchar(10)	IN
@DPC	nvarchar(8)	IN
@LOT	nvarchar(5)	IN
@COUNTYCODEID	uniqueidentifier	IN
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@LASTVALIDATIONATTEMPTDATE	datetime	IN
@VALIDATIONMESSAGE	nvarchar(100)	IN
@CERTIFICATIONDATA	int	IN
@DONOTCALL	bit	IN
@DONOTEMAIL	bit	IN
@SITEID	uniqueidentifier	IN
@CONSTITUENTSECURITYGROUPID	uniqueidentifier	IN
@CONSTITUENCYCODEID	uniqueidentifier	IN
@INFOSOURCECODEID	uniqueidentifier	IN
Definition

Copy


            CREATE procedure dbo.USP_BATCHREVENUE_ADDHOUSEHOLD
            (
                @ID uniqueidentifier output,
                @NAME nvarchar(100),
                @DESCRIPTION nvarchar(300) = '',
                @GIVESANONYMOUSLY bit = 0,
                @ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
                @ADDRESS_COUNTRYID uniqueidentifier = null,
                @ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
                @ADDRESS_CITY nvarchar(50) = '',
                @ADDRESS_STATEID uniqueidentifier = null,
                @ADDRESS_POSTCODE nvarchar(12) = '',
                @PHONE_PHONETYPECODEID uniqueidentifier = null,
                @PHONE_NUMBER nvarchar(100) = '',
                @EMAIL_EMAILADDRESSTYPECODEID uniqueidentifier = null,
                @EMAIL_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
                @WEBADDRESS dbo.UDT_WEBADDRESS = '',

                -- Primary member fields

                @PRIMARYCONTACTID uniqueidentifier = null,
                @PRIMARYCONTACT_KEYNAME nvarchar(100) = '',
                @PRIMARYCONTACT_FIRSTNAME nvarchar(50) = '',
                @PRIMARYCONTACT_MIDDLENAME nvarchar(50) = '',
                @PRIMARYCONTACT_TITLECODEID uniqueidentifier = null,
                @PRIMARYCONTACT_SUFFIXCODEID uniqueidentifier = null,
                @PRIMARYCONTACT_COPYHOUSEHOLDCONTACT bit = 0,

                -- Secondary member fields

                @SECONDMEMBERID uniqueidentifier = null,
                @SECONDMEMBER_KEYNAME nvarchar(100) = '',
                @SECONDMEMBER_FIRSTNAME nvarchar(50) = '',
                @SECONDMEMBER_MIDDLENAME nvarchar(50) = '',
                @SECONDMEMBER_TITLECODEID uniqueidentifier = null,
                @SECONDMEMBER_SUFFIXCODEID uniqueidentifier = null,
                @SECONDMEMBER_COPYHOUSEHOLDCONTACT bit = 0,
                @SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID uniqueidentifier = null,
                @SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID uniqueidentifier = null,
                @SECONDMEMBER_RELATIONSHIP_STARTDATE datetime = null,
                @SECONDMEMBER_RELATIONSHIP_ISSPOUSE bit = 0,

                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @ADDRESS_DONOTMAIL bit = 0,
                @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
                @OMITFROMVALIDATION bit = 0,
                @CART nvarchar(10) = '',
                @DPC nvarchar(8) = '',
                @LOT nvarchar(5) = '',
                @COUNTYCODEID uniqueidentifier = null,
                @CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
                @LASTVALIDATIONATTEMPTDATE datetime = null,
                @VALIDATIONMESSAGE nvarchar(100) = '',
                @CERTIFICATIONDATA integer = 0,
                @DONOTCALL bit = 0,
                @DONOTEMAIL bit = 0,
                @SITEID uniqueidentifier = null, 
                @CONSTITUENTSECURITYGROUPID uniqueidentifier = null,
                @CONSTITUENCYCODEID uniqueidentifier = null,
                @INFOSOURCECODEID uniqueidentifier = null
            )
            as
                set nocount on;

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

                -- Validate that the primary and secondary members aren't the same

                if @PRIMARYCONTACTID = @SECONDMEMBERID
                    raiserror('ERRPRIMARYANDSECONDMEMBERSAME', 13, 1)

                if @ADDRESS_DONOTMAIL = 0
                    set @ADDRESS_DONOTMAILREASONCODEID = null

                declare @NAMEFORMATFUNCTIONID uniqueidentifier;

                -- if the user did not specify a custom name, then they must have specified a primary user.

                -- we will recalculate the name of the household at the end if we are calculating it.

                if @NAME is null or @NAME = ''
                    begin
                        -- We will regenerate the name later, but we need a placeholder.

                        if @PRIMARYCONTACTID is not null or (@PRIMARYCONTACT_KEYNAME is not null and @PRIMARYCONTACT_KEYNAME <> '')
                            set @NAME = 'Household';
                        else
                            raiserror('ERR_CUSTOMNAMEREQUIRED', 13, 1)

                        select top(1) @NAMEFORMATFUNCTIONID = NAMEFORMATFUNCTIONID from dbo.HOUSEHOLDINFO order by DATEADDED;
                    end
                else
                    set @NAMEFORMATFUNCTIONID = null;

                -- create the batch constituent record for the household

                insert into dbo.BATCHREVENUECONSTITUENT
                (
                    ID, 
                    ISGROUP, 
                    KEYNAME, 
                    GIVESANONYMOUSLY, 
                    GROUPTYPECODE,
                    GROUPTYPEID,
                    GROUPDESCRIPTION,
                    NAMEFORMATFUNCTIONID,
                    ADDRESSTYPECODEID,
                    COUNTRYID,
                    STATEID,
                    ADDRESSBLOCK,
                    CITY,
                    POSTCODE,
                    OMITFROMVALIDATION,
                    CART,
                    DPC,
                    LOT,
                    COUNTYCODEID,
                    CONGRESSIONALDISTRICTCODEID,
                    LASTVALIDATIONATTEMPTDATE,
                    VALIDATIONMESSAGE,
                    CERTIFICATIONDATA,
                    PHONETYPECODEID,
                    NUMBER,
                    EMAILADDRESSTYPECODEID,
                    EMAILADDRESS,
                    WEBADDRESS,
                    CURRENTAPPUSERID,
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
                    DONOTMAIL,
                    DONOTMAILREASONCODEID,
                    DONOTCALL,
                    DONOTEMAIL,
                    SITEID, 
                    CONSTITUENTSECURITYGROUPID,
                    CONSTITUENCYCODEID,
                    INFOSOURCECODEID
                )
                values
                (
                    @ID, 
                    1, 
                    @NAME, 
                    @GIVESANONYMOUSLY, 
                    0, -- Indicates household

                    null,
                    @DESCRIPTION,
                    @NAMEFORMATFUNCTIONID,
                    @ADDRESS_ADDRESSTYPECODEID,
                    @ADDRESS_COUNTRYID,
                    @ADDRESS_STATEID,
                    @ADDRESS_ADDRESSBLOCK,
                    @ADDRESS_CITY,
                    @ADDRESS_POSTCODE,
                    @OMITFROMVALIDATION,
                    @CART,
                    @DPC,
                    @LOT,
                    @COUNTYCODEID,
                    @CONGRESSIONALDISTRICTCODEID,
                    @LASTVALIDATIONATTEMPTDATE,
                    @VALIDATIONMESSAGE,
                    @CERTIFICATIONDATA,
                    @PHONE_PHONETYPECODEID,
                    @PHONE_NUMBER,
                    @EMAIL_EMAILADDRESSTYPECODEID,
                    @EMAIL_EMAILADDRESS,
                    @WEBADDRESS,
                    @CURRENTAPPUSERID,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,
                    @ADDRESS_DONOTMAIL,
                    @ADDRESS_DONOTMAILREASONCODEID,
                    @DONOTCALL,
                    @DONOTEMAIL,
                    @SITEID, 
                    @CONSTITUENTSECURITYGROUPID,
                    @CONSTITUENCYCODEID,
                    @INFOSOURCECODEID
                );

                -- Create primary member record if needed

                declare @BATCHREVENUEPRIMARYCONTACTID uniqueidentifier                            
                if @PRIMARYCONTACTID is not null or coalesce(@PRIMARYCONTACT_KEYNAME, '') <> ''
                begin
                    exec dbo.USP_BATCHREVENUE_ADDGROUPMEMBER @BATCHREVENUEPRIMARYCONTACTID output, @ID, @PRIMARYCONTACTID,
                        @PRIMARYCONTACT_KEYNAME, @PRIMARYCONTACT_FIRSTNAME, @PRIMARYCONTACT_MIDDLENAME, @PRIMARYCONTACT_TITLECODEID,
                        @PRIMARYCONTACT_SUFFIXCODEID, @PRIMARYCONTACT_COPYHOUSEHOLDCONTACT, 1, @CHANGEAGENTID, @CURRENTAPPUSERID
                end

                -- Create additional member record if needed

                declare @BATCHREVENUESECONDMEMBERID uniqueidentifier    
                if @SECONDMEMBERID is not null or coalesce(@SECONDMEMBER_KEYNAME, '') <> ''
                begin
                    exec dbo.USP_BATCHREVENUE_ADDGROUPMEMBER @BATCHREVENUESECONDMEMBERID output, @ID, @SECONDMEMBERID,
                        @SECONDMEMBER_KEYNAME, @SECONDMEMBER_FIRSTNAME, @SECONDMEMBER_MIDDLENAME, @SECONDMEMBER_TITLECODEID,
                        @SECONDMEMBER_SUFFIXCODEID, @SECONDMEMBER_COPYHOUSEHOLDCONTACT, 0, @CHANGEAGENTID, @CURRENTAPPUSERID

                    -- Create relationship between primary and second member if a relationship type is specified

                    if @SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID is not null
                    begin
                        -- Verify that if the members already exist, they aren't already related

                        if @PRIMARYCONTACTID is null or @SECONDMEMBERID is null or
                            not exists(select top 1 id from dbo.RELATIONSHIP where RELATIONSHIPCONSTITUENTID = @PRIMARYCONTACTID and RECIPROCALCONSTITUENTID = @SECONDMEMBERID)
                        begin                    
                            declare @SECONDMEMBER_PRIMARYRELATIONSHIPEXISTS bit
                            declare @SECONDMEMBER_PRIMARYMATCHFACTOR decimal(5,2) 
                            declare @SECONDMEMBER_RECIPROCALRELATIONSHIPEXISTS bit
                            declare @SECONDMEMBER_RECIPROCALMATCHFACTOR decimal(5,2)                        
                            declare @SECONDMEMBER_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier 
                            declare @SECONDMEMBER_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier

                            select @SECONDMEMBER_PRIMARYRELATIONSHIPEXISTS = 1,
                                @SECONDMEMBER_PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
                                @SECONDMEMBER_PRIMARYMATCHFACTOR= RRD.MATCHFACTOR
                            from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD      
                            where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID

                            select @SECONDMEMBER_RECIPROCALRELATIONSHIPEXISTS = 1,
                                @SECONDMEMBER_RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
                                @SECONDMEMBER_RECIPROCALMATCHFACTOR= RRD.MATCHFACTOR
                            from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD      
                            where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID

                            insert into dbo.BATCHREVENUECONSTITUENTRELATION
                            (
                                CONSTITUENTID,
                                RELATIONID,
                                RELATIONSHIPTYPECODEID,
                                RECIPROCALTYPECODEID,
                                STARTDATE,
                                PRIMARYRELATIONSHIPEXISTS,
                                PRIMARYMATCHFACTOR,
                                PRIMARYRECOGNITIONTYPECODEID,
                                RECIPROCALRELATIONSHIPEXISTS,
                                RECIPROCALMATCHFACTOR,
                                RECIPROCALRECOGNITIONTYPECODEID,
                                ISSPOUSE,
                                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                            )
                            values
                            (
                                @BATCHREVENUEPRIMARYCONTACTID,
                                @BATCHREVENUESECONDMEMBERID,
                                @SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID,
                                @SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID,
                                @SECONDMEMBER_RELATIONSHIP_STARTDATE,
                                ISNULL(@SECONDMEMBER_PRIMARYRELATIONSHIPEXISTS,0),
                                ISNULL(@SECONDMEMBER_PRIMARYMATCHFACTOR,0),
                                @SECONDMEMBER_PRIMARYRECOGNITIONTYPECODEID,
                                ISNULL(@SECONDMEMBER_RECIPROCALRELATIONSHIPEXISTS,0),
                                ISNULL(@SECONDMEMBER_RECIPROCALMATCHFACTOR,0),
                                @SECONDMEMBER_RECIPROCALRECOGNITIONTYPECODEID,
                                @SECONDMEMBER_RELATIONSHIP_ISSPOUSE,
                                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                            )
                        end
                    end
                end

                -- Update the household name

                if @NAMEFORMATFUNCTIONID is not null
                    update dbo.BATCHREVENUECONSTITUENT
                        set BATCHREVENUECONSTITUENT.KEYNAME = left(dbo.UFN_BATCHREVENUECONSTITUENT_NAMEFORMAT_FROMID(@NAMEFORMATFUNCTIONID, @BATCHREVENUEPRIMARYCONTACTID), 100)        
                        where BATCHREVENUECONSTITUENT.ID = @ID