USP_FAFNFGCAMPAIGNLEVEL_COPY

CREATE procedure dbo.USP_FAFNFGCAMPAIGNLEVEL_COPY
(
  @SOURCENFGID uniqueidentifier,
  @TARGETNFGID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
begin
  SET NOCOUNT ON;
    begin try
     declare @CURRENTDATE datetime
   set @CURRENTDATE = getdate()

  if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
  CREATE TABLE #TEMPLEVEL
  (
    ID int identity(1,1),
    CURRENTLEVELID uniqueidentifier,
    NFGCAMPAIGNID uniqueidentifier,
    Name nvarchar(100),
    CONTACTID uniqueidentifier,
    TOTALREVENUEGOAL money,
    DONORSGOAL int,
    PARTICIPANTSGOAL int,
    GROUPSGOAL int,
    COMMUNICATIONSGOAL int,
    PREVIOUSLEVELID uniqueidentifier,
    PARENTID uniqueidentifier
  )

    insert into #TEMPLEVEL
        (CURRENTLEVELID, NFGCAMPAIGNID, NAME, CONTACTID,TOTALREVENUEGOAL,DONORSGOAL,PARTICIPANTSGOAL,GROUPSGOAL,COMMUNICATIONSGOAL,PREVIOUSLEVELID,PARENTID)
   select newid(), @TARGETNFGID, NAME, CONTACTID, TOTALREVENUEGOAL, DONORSGOAL, PARTICIPANTSGOAL, GROUPSGOAL,COMMUNICATIONSGOAL,ID,(select PARENT.ID from dbo.FAFNFGCAMPAIGNLEVEL as PARENT where PARENT.HIERARCHYPATH = FNCL.HIERARCHYPATH.GetAncestor(1)) 
   from dbo.FAFNFGCAMPAIGNLEVEL FNCL where FNCL.NFGCAMPAIGNID = @SOURCENFGID
   Order by FNCL.HIERARCHYPATH.GetLevel()

  declare @COUNT int, @INDEX int
  declare @PARENTID uniqueidentifier, @CURRENTPARENTID uniqueidentifier
     select @COUNT = COUNT(*),@Index = 1  from #TEMPLEVEL
  while @Index< = @Count
  begin
    select @PARENTID = PARENTID from #TEMPLEVEL where ID = @INDEX
    select @CURRENTPARENTID = CURRENTLEVELID from #TEMPLEVEL where PREVIOUSLEVELID = @PARENTID
    insert into dbo.FAFNFGCAMPAIGNLEVEL 
    (ID, NFGCAMPAIGNID, NAME, HIERARCHYPATH,CONTACTID,TOTALREVENUEGOAL,DONORSGOAL,PARTICIPANTSGOAL,GROUPSGOAL,COMMUNICATIONSGOAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
   select CURRENTLEVELID, NFGCAMPAIGNID, NAME, dbo.UFN_LEVELHIERARCHY_GETNEWDESCENDANTVALUE(NFGCAMPAIGNID, @CURRENTPARENTID), CONTACTID, TOTALREVENUEGOAL, DONORSGOAL, PARTICIPANTSGOAL, GROUPSGOAL,
    COMMUNICATIONSGOAL, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE from #TEMPLEVEL where ID = @INDEX    
    set @Index = @Index + 1
  end

  insert into dbo.FAFNFGCAMPAIGNLEVELDISPLAYSITE (ID, NFGCAMPAIGNLEVELID, SITEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)  
  select newid(), t.CURRENTLEVELID, SITEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE 
  from dbo.FAFNFGCAMPAIGNLEVELDISPLAYSITE s join #TEMPLEVEL t on s.NFGCAMPAIGNLEVELID = t.PREVIOUSLEVELID

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

end