USP_FAF_ADDRESSBOOK_DEMOTION_INSERT

create procedure dbo.USP_FAF_ADDRESSBOOK_DEMOTION_INSERT(
     @GROUPID                  uniqueidentifier = NULL,
     @EVENTID                  uniqueidentifier = NULL,
     @COMPANYLEADERCONSTITUENTID     uniqueidentifier = NULL,
     @ISDEMOTEDTOHOUSEHOLD     bit
)
as
begin
    -- get the list from company individual and household leaders

   -- get active events

  DECLARE @COMPANYLIST TABLE
  (
       ID                    int IDENTITY(1,1),
       GROUPNAME             varchar(200),
       GROUPID               uniqueidentifier,
       CONSTITUENTNAME       varchar(400),
       CONSTITUENTID         uniqueidentifier
  )

  --temporarily switch back to company

  Update dbo.TEAMExtension Set TypeCode = 2 Where TEAMFUNDRAISINGTEAMID =  @GROUPID

  --insert household leaders

  if @ISDEMOTEDTOHOUSEHOLD = 0
  begin
      insert into @COMPANYLIST(GROUPNAME, GROUPID, CONSTITUENTNAME, CONSTITUENTID)
        select   
             TM.NAME,    
             TM.ID,  
             C.name,  
             C.ID   
        from dbo.TEAMEXTENSION TE    
        INNER JOIN dbo.TEAMFUNDRAISINGTEAM TM  
            ON TE.TEAMFUNDRAISINGTEAMID = TM.ID  
        INNER JOIN dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC  --retrieve team leaders  

            ON TE.TEAMFUNDRAISINGTEAMID = TC.TEAMFUNDRAISINGTEAMID  
        INNER JOIN dbo.CONSTITUENT C  
            ON TC.CONSTITUENTID = C.ID    
        WHERE TE.EVENTID = @EVENTID
        AND TE.TYPECODE = 3 AND TE.STATUSCODE = 0 
        and TM.PARENTTEAMID = @GROUPID
    end

    -- insert individuals

    insert into @COMPANYLIST(GROUPNAME, GROUPID, CONSTITUENTNAME, CONSTITUENTID)
    select TEAMNAME, TEAMID, name, ID
    from dbo.UFN_FAF_COMPANYINDIVIDUALLIST(@COMPANYLEADERCONSTITUENTID, @EVENTID)

     -- complete type change

    if @ISDEMOTEDTOHOUSEHOLD = 0
        Update dbo.TEAMExtension Set TypeCode = 1 Where TEAMFUNDRAISINGTEAMID =  @GROUPID
    else
        Update dbo.TEAMExtension Set TypeCode = 3 Where TEAMFUNDRAISINGTEAMID =  @GROUPID

    declare @count  int
    set @count = 1
    declare @CONSTITUENTID  uniqueidentifier

    declare @NUMCONSTITUENT   int
    Select @NUMCONSTITUENT = max(ID) from @COMPANYLIST

    while @count <= @NUMCONSTITUENT
    begin 
        Select @CONSTITUENTID = CONSTITUENTID from @COMPANYLIST where id = @count

        Exec dbo.USP_FAFADDRESSBOOK_REGISTRANT_ADD @GROUPID, @CONSTITUENTID

        -- add loop count

        set @count = @count + 1
    end



end