USP_DATAFORMTEMPLATE_EDITLOAD_FAFPROGRAM

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_FAFPROGRAM(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @CURRENTAPPUSERID uniqueidentifier,
      @NAME nvarchar(100) = NULL output,  
    @DESCRIPTION nvarchar(255) = null output,  
    @SITES xml = null output,
      @SITEREQUIRED bit = null output,    
    @PREVIOUSCAMPAIGNID uniqueidentifier = null output,
    @SELECTEDEVENTS xml = null output
)
as

    set nocount on;
    set @DATALOADED = 0
    set @TSLONG = 0
  declare @DEFAULTSITEID uniqueidentifier,
          @USERDEFAULTSITEID uniqueidentifier        
  set @SITEREQUIRED = dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID);

select @USERDEFAULTSITEID = SITEID from dbo.APPUSER where APPUSER.ID = @CURRENTAPPUSERID 

  set @DEFAULTSITEID = dbo.UFN_APPUSER_DEFAULTSITEFORUSER(@CURRENTAPPUSERID)

    if @USERDEFAULTSITEID is not null
    select @SITES = (
      SELECT newID() as ID, S.ID as [SITEID], S.NAME
     FROM dbo.[SITE] S where S.ID = @USERDEFAULTSITEID
     for xml raw('ITEM'),type,elements,root('SITES'),BINARY BASE64 )

  if @SITEREQUIRED = 1
  begin 
    if @USERDEFAULTSITEID is null
      raiserror('You are not assigned to a default site. To continue, contact your administrator and verify your default site setup.', 13,1);
    else
      if dbo.UFN_APPUSER_DEFAULTSITEFORUSER(@CURRENTAPPUSERID) is null
         raiserror('Your default site is not included in your role''s record access.  To continue, contact your administrator and update your role.', 13,1);
  end


    select
        @DATALOADED = 1,
        @TSLONG = FP.TSLONG,
        @NAME = FP.NAME,
        @DESCRIPTION = FP.DESCRIPTION,    
    @PREVIOUSCAMPAIGNID = FP.PREVIOUSCAMPAIGNID
    from dbo.FAFPROGRAM FP
    where FP.ID = @ID

select @SELECTEDEVENTS = (select  newID() as ID, E.ID as EVENTID,E.NAME AS EVENTNAME, cast(1 as bit) as HASPERMISSION
                 from EVENTEXTENSION EX
                 inner join EVENT E on E.ID=EX.EVENTID
                where FAFPROGRAMID=@ID
                 for xml raw('ITEM'),type,elements,root('SELECTEDEVENTS'),BINARY BASE64)

 select @SITES = (
      SELECT FS.[ID], FS.[SITEID], S.NAME
     FROM dbo.[UFN_FAFPROGRAM_GETSITES](@ID) FS
     join dbo.[SITE] S on S.ID = FS.SITEID
     for xml raw('ITEM'),type,elements,root('SITES'),BINARY BASE64 )

    return 0;