USP_RESERVATION_GETCONTACTINFO

Provides a way for a reservation to determine contact info based on a constituent ID and contact relationship ID passed in.
Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@CONTACTRELATIONSHIPID	uniqueidentifier	IN
@CONTACTCONSTITUENTID	uniqueidentifier	INOUT
@CONTACTADDRESSID	uniqueidentifier	INOUT
@CONTACTEMAILADDRESSID	uniqueidentifier	INOUT
@CONTACTPHONEID	uniqueidentifier	INOUT
Definition

Copy


            CREATE procedure dbo.USP_RESERVATION_GETCONTACTINFO
            (
                @CONSTITUENTID uniqueidentifier,
                @CONTACTRELATIONSHIPID uniqueidentifier,
                @CONTACTCONSTITUENTID uniqueidentifier = null output,
                @CONTACTADDRESSID uniqueidentifier = null output,
                @CONTACTEMAILADDRESSID uniqueidentifier = null output,
                @CONTACTPHONEID uniqueidentifier = null output
            )
            as
                declare @ISGROUP bit;
                declare @ISORGANIZATION bit;

                select
                    @ISGROUP = ISGROUP,
                    @ISORGANIZATION = ISORGANIZATION
                from
                    dbo.CONSTITUENT
                where
                    ID = @CONSTITUENTID;

                -- Validation for the contact relationship and set contact

                if @ISORGANIZATION is null  -- @CONSTITUENTID does not exist in the CONSTITUENT table

                    raiserror('ERR_CONSTITUENTIDNOTVALID', 13, 1);

                if @ISORGANIZATION = 0
                    begin
                        if @CONTACTRELATIONSHIPID is not null
                            raiserror('ERR_CONTACTNOTALLOWED', 13, 1);

                        if @ISGROUP = 1
                            select @CONTACTCONSTITUENTID = MEMBERID from dbo.GROUPMEMBER
                            where GROUPID = @CONSTITUENTID and ISPRIMARY = 1;
                        else
                            set @CONTACTCONSTITUENTID = @CONSTITUENTID;

                    end

                else if @CONTACTRELATIONSHIPID is null
                    raiserror('ERR_CONTACTREQUIRED', 13, 1);

                else if dbo.UFN_RELATIONSHIP_ISCURRENTCONTACT(@CONTACTRELATIONSHIPID, @CONSTITUENTID) = 0
                    raiserror('ERR_CONTACTINVALID', 13, 1);

                else
                    select 
                        @CONTACTCONSTITUENTID = RECIPROCALCONSTITUENTID 
                    from dbo.RELATIONSHIP
                    where ID = @CONTACTRELATIONSHIPID;

                if @CONTACTRELATIONSHIPID is not null
                    begin
                        -- No longer storing the contact info ids on the sales order

                        --    Now storing the contact relationship id

                        set  @CONTACTADDRESSID = null;                    
                        set  @CONTACTEMAILADDRESSID = null;                    
                        set  @CONTACTPHONEID = null;
                    end

                else
                    begin
                        select @CONTACTADDRESSID = ADDRESS.ID from dbo.ADDRESS
                        where ADDRESS.CONSTITUENTID = @CONTACTCONSTITUENTID and ADDRESS.ISPRIMARY = 1;

                        select @CONTACTEMAILADDRESSID = EMAILADDRESS.ID from dbo.EMAILADDRESS
                        where EMAILADDRESS.CONSTITUENTID = @CONTACTCONSTITUENTID and EMAILADDRESS.ISPRIMARY = 1;

                        select @CONTACTPHONEID = PHONE.ID from dbo.PHONE
                        where PHONE.CONSTITUENTID = @CONTACTCONSTITUENTID and PHONE.ISPRIMARY = 1;
                    end

                return 0;