USP_ORGANIZATION_ADD_BYINDIVIDUAL

Adds an organization by an individual and create the relationship.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@INDIVIDUALCONSTITUENTID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@WEBADDRESS	UDT_WEBADDRESS	IN
@ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	IN
@ADDRESS_COUNTRYID	uniqueidentifier	IN
@ADDRESS_STATEID	uniqueidentifier	IN
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN
@ADDRESS_CITY	nvarchar(50)	IN
@ADDRESS_POSTCODE	nvarchar(12)	IN
@ADDRESS_DONOTMAIL	bit	IN
@ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	IN
@PHONE_PHONETYPECODEID	uniqueidentifier	IN
@PHONE_NUMBER	nvarchar(100)	IN
@EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_ORGANIZATION_ADD_BYINDIVIDUAL (  
    @ID uniqueidentifier = null output,
    @INDIVIDUALCONSTITUENTID uniqueidentifier,
    @NAME nvarchar(100),  
    @WEBADDRESS dbo.UDT_WEBADDRESS = '',  
    @ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,  
    @ADDRESS_COUNTRYID uniqueidentifier = null,  
    @ADDRESS_STATEID uniqueidentifier = null,  
    @ADDRESS_ADDRESSBLOCK nvarchar(150) = '',  
    @ADDRESS_CITY nvarchar(50) = '',  
    @ADDRESS_POSTCODE nvarchar(12) = '',  
    @ADDRESS_DONOTMAIL bit = 0,
    @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,  
    @PHONE_PHONETYPECODEID uniqueidentifier = null,  
    @PHONE_NUMBER nvarchar(100) = '',  
    @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,  
    @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',  
    @CURRENTAPPUSERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null
) 
as  
set nocount on;  

begin try

    declare @LEFTPOSTCODECOUNT int;
    declare @OVERALLMATCHTHRESHOLD numeric(5, 2);  

    if @ID = '00000000-0000-0000-0000-000000000000'
        set @ID = null;

    if @ID is null
    begin
        select top 1 @ID = C.ID
        from dbo.CONSTITUENT C  
        inner join dbo.[ADDRESS] A on C.ID = A.CONSTITUENTID  
        where C.ISORGANIZATION = 1   
        and  C.KEYNAME = @NAME
        and  A.POSTCODE = @ADDRESS_POSTCODE  
        and A.ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK
        order by C.DATECHANGED desc;
    end

    if dbo.UFN_VALIDWEBADDRESS(@WEBADDRESS) != 1
        set @WEBADDRESS = 'http://' + @WEBADDRESS;

    if dbo.UFN_VALIDWEBADDRESS(@WEBADDRESS) != 1
        set @WEBADDRESS = '';

  if @ID is null begin
        set @ID = newid();
        exec dbo.USP_ORGANIZATION_ADD
        @ID = @ID,
        @NAME = @NAME,  
        @WEBADDRESS = @WEBADDRESS,  
        @ADDRESS_ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID,  
        @ADDRESS_COUNTRYID = @ADDRESS_COUNTRYID,  
        @ADDRESS_STATEID = @ADDRESS_STATEID,  
        @ADDRESS_ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK,  
        @ADDRESS_CITY = @ADDRESS_CITY,  
        @ADDRESS_POSTCODE = @ADDRESS_POSTCODE,  
        @ADDRESS_DONOTMAIL = @ADDRESS_DONOTMAIL,
        @ADDRESS_DONOTMAILREASONCODEID = @ADDRESS_DONOTMAILREASONCODEID,  
        @PHONE_PHONETYPECODEID = @PHONE_PHONETYPECODEID,  
        @PHONE_NUMBER = @PHONE_NUMBER,  
        @EMAILADDRESS_EMAILADDRESSTYPECODEID = @EMAILADDRESS_EMAILADDRESSTYPECODEID,  
        @EMAILADDRESS_EMAILADDRESS = @EMAILADDRESS_EMAILADDRESS,  
        @CURRENTAPPUSERID = @CURRENTAPPUSERID,
        @CHANGEAGENTID = @CHANGEAGENTID;
    end

    declare @RELATIONSHIPTYPECODEID uniqueidentifier = null;  
    declare @RECIPROCALTYPECODEID uniqueidentifier = null;

    select top 1 
        @RELATIONSHIPTYPECODEID = PRIMARYBUSINESSRELATIONSHIPTYPECODEID, 
        @RECIPROCALTYPECODEID = PRIMARYBUSINESSRECIPROCALTYPECODEID
    from dbo.NETCOMMUNITYDEFAULTCODEMAP(nolock)

    if @RELATIONSHIPTYPECODEID is not null and @RECIPROCALTYPECODEID is not null
        and @ID is not null and @INDIVIDUALCONSTITUENTID is not null
        and @ID <> @INDIVIDUALCONSTITUENTID
        if not exists (select ID from RELATIONSHIP(nolock) 
                        where RELATIONSHIPCONSTITUENTID = @ID
                        and RECIPROCALCONSTITUENTID = @INDIVIDUALCONSTITUENTID
                        and RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID
                        and RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID)
            exec dbo.USP_DATAFORMTEMPLATE_ADD_INDTOGROUP_RELATIONSHIP
                @CONSTITUENTID = @ID,  
                @RECIPROCALCONSTITUENTID = @INDIVIDUALCONSTITUENTID,  
                @RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID ,
                @RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID,
                @COMMENTS = ''  

end try

begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch

return 0;