USP_RELATIONSHIP_ADDFORORGANIZATIONGROUP

create procedure dbo.USP_RELATIONSHIP_ADDFORORGANIZATIONGROUP (
  @ORGANIZATIONCONSTITUENTID uniqueidentifier = null,
  @TEAMFUNDRAISINGTEAMID uniqueidentifier =  null
)
as
begin

  -- do work

  declare @GROUPCONSTITUENTID uniqueidentifier = null; 
  declare @TEAMTYPECODE int = null;
  declare @RELATIONSHIPTYPECODEID uniqueidentifier = null;  
    declare @RECIPROCALTYPECODEID uniqueidentifier = null;

  if @TEAMFUNDRAISINGTEAMID is not null
    select @GROUPCONSTITUENTID = TEAMCONSTITUENTID, @TEAMTYPECODE = TYPECODE
    from TEAMEXTENSION (nolock) where TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID;

  select @RELATIONSHIPTYPECODEID = ID from RELATIONSHIPTYPECODE (nolock) where DESCRIPTION = 'FAF Organization';

  if @TEAMTYPECODE = 1
    select @RECIPROCALTYPECODEID = ID from RELATIONSHIPTYPECODE (nolock) where DESCRIPTION = 'FAF Team';

  if @TEAMTYPECODE = 2
    select @RECIPROCALTYPECODEID = ID from RELATIONSHIPTYPECODE (nolock) where DESCRIPTION = 'FAF Company';

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