USP_MKTSEGMENTPASSIVE_COPY

CREATE procedure dbo.[USP_MKTSEGMENTPASSIVE_COPY]
(
  @ID uniqueidentifier output,
  @CHANGEAGENTID uniqueidentifier
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @SOURCESEGMENTID uniqueidentifier;
  declare @TARGETSEGMENTID uniqueidentifier;
  declare @SEGMENTTYPECODE tinyint;
  declare @UNIQUENAME nvarchar(100);

  declare @DATALOADED bit;
  declare @QUERYVIEWCATALOGID uniqueidentifier;
  declare @NAME nvarchar(100);
  declare @DESCRIPTION nvarchar(255);
  declare @SITEID uniqueidentifier;
  declare @SEGMENTCATEGORYCODEID uniqueidentifier;
  declare @CODEVALUEID uniqueidentifier;
  declare @CODE nvarchar(10);
  declare @VENDORID uniqueidentifier;
  declare @IMPRESSIONS integer;
  declare @IMPRESSIONCALCULATIONMETHODCODE tinyint;
  declare @GROUPS xml;
  declare @ISINUSE bit;
  declare @ALLOWCODEUPDATE bit;
  declare @TSLONG bigint;

  declare @PARENTMEDIAOUTLETSEGMENTID uniqueidentifier;
  declare @SCHEDULESTARTTIME time(0);
  declare @SCHEDULEENDTIME time(0);
  declare @SCHEDULEDURATIONHOURS integer;
  declare @SCHEDULEDURATIONMINUTES integer;
  declare @SCHEDULEDURATIONSECONDS integer;

  declare @LOCATIONCOUNTRYID uniqueidentifier;
  declare @LOCATIONSTATEID uniqueidentifier;
  declare @LOCATIONPOSTCODE nvarchar(12);
  declare @LOCATIONCITY nvarchar(50);
  declare @LOCATIONADDRESSBLOCK nvarchar(150);

  declare @TEMPGROUPS table ([ID] uniqueidentifier, [SEGMENTGROUPID] uniqueidentifier, [SEGMENTGROUPSELECTED] bit);

  begin try
    if @CHANGEAGENTID is null  
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = getdate();
    set @SOURCESEGMENTID = @ID;
    set @TARGETSEGMENTID = newid();

    select @SEGMENTTYPECODE = [SEGMENTTYPECODE]
    from dbo.[MKTSEGMENT]
    where [ID] = @SOURCESEGMENTID;

    if @SEGMENTTYPECODE in (6, 7, 8)
      begin
        if @SEGMENTTYPECODE = 6
          exec dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTMEDIAOUTLET] 
            @SOURCESEGMENTID, 
            null, 
            @DATALOADED output, 
            @QUERYVIEWCATALOGID output, 
            @NAME output, 
            @DESCRIPTION output, 
            @SEGMENTCATEGORYCODEID output, 
            @CODE output, null, 
            @CODEVALUEID output, 
            @VENDORID output, 
            @IMPRESSIONS output, 
            @IMPRESSIONCALCULATIONMETHODCODE output, 
            @GROUPS output, 
            @ISINUSE output, 
            @ALLOWCODEUPDATE output, 
            null, 
            @TSLONG output, 
            @SITEID output, 
            null;

        else if @SEGMENTTYPECODE = 7
          exec dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTTIMESLOT] 
            @SOURCESEGMENTID, 
            null, 
            @DATALOADED output, 
            @QUERYVIEWCATALOGID output, 
            @NAME output, 
            @DESCRIPTION output, 
            @SEGMENTCATEGORYCODEID output, 
            @CODE output, 
            null, 
            @CODEVALUEID output, 
            @PARENTMEDIAOUTLETSEGMENTID output, 
            @SCHEDULESTARTTIME output, 
            @SCHEDULEENDTIME output, 
            @SCHEDULEDURATIONHOURS output, 
            @SCHEDULEDURATIONMINUTES output, 
            @SCHEDULEDURATIONSECONDS output, 
            @IMPRESSIONS output, 
            @IMPRESSIONCALCULATIONMETHODCODE output, 
            @GROUPS output, 
            @ISINUSE output, 
            @ALLOWCODEUPDATE output, 
            null, 
            @TSLONG output, 
            @SITEID output, 
            null;

        else if @SEGMENTTYPECODE = 8
          exec dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTMARKETINGLOCATION] 
            @SOURCESEGMENTID, 
            null, 
            @DATALOADED output, 
            @QUERYVIEWCATALOGID output, 
            @NAME output, 
            @DESCRIPTION output, 
  @SEGMENTCATEGORYCODEID output, 
            @CODE output, 
            null, 
            @CODEVALUEID output, 
            @VENDORID output, 
            @LOCATIONCOUNTRYID output, 
            @LOCATIONSTATEID output, 
            @LOCATIONPOSTCODE output, 
            @LOCATIONCITY output, 
            @LOCATIONADDRESSBLOCK output, 
            @IMPRESSIONS output, 
            @IMPRESSIONCALCULATIONMETHODCODE output,
            @GROUPS output, 
            @ISINUSE output, 
            @ALLOWCODEUPDATE output, 
            null, 
            @TSLONG output, 
            @SITEID output, 
            null;

        set @UNIQUENAME = dbo.[UFN_MKTSEGMENT_GETUNIQUENAME](@TARGETSEGMENTID, @NAME, null);

        insert into @TEMPGROUPS
          select
            null, 
            T.c.value('(SEGMENTGROUPID)[1]', 'uniqueidentifier'), 
            1  --segments groups loaded have all been selected

          from 
            @GROUPS.nodes('/GROUPS/ITEM') T(c);

        select @GROUPS = (select [ID], [SEGMENTGROUPID], [SEGMENTGROUPSELECTED]
                          from @TEMPGROUPS
                          for xml raw('ITEM'), type, elements, root('GROUPS'), binary base64);

        if @SEGMENTTYPECODE = 6
          exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTMEDIAOUTLET] 
            @TARGETSEGMENTID, 
            @CHANGEAGENTID, 
            @QUERYVIEWCATALOGID, 
            @UNIQUENAME, 
            @DESCRIPTION, 
            @SEGMENTCATEGORYCODEID, 
            @CODE, 
            @CODEVALUEID, 
            @VENDORID, 
            @IMPRESSIONS, 
            @IMPRESSIONCALCULATIONMETHODCODE, 
            @GROUPS, 
            @SITEID;

        else if @SEGMENTTYPECODE = 7
          exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTTIMESLOT] 
            @TARGETSEGMENTID, 
            @CHANGEAGENTID, 
            @QUERYVIEWCATALOGID, 
            @UNIQUENAME, 
            @DESCRIPTION, 
            @SEGMENTCATEGORYCODEID, 
            @CODE, 
            @CODEVALUEID, 
            @PARENTMEDIAOUTLETSEGMENTID, 
            @SCHEDULESTARTTIME, 
            @SCHEDULEENDTIME, 
            @SCHEDULEDURATIONHOURS,
            @SCHEDULEDURATIONMINUTES,
            @SCHEDULEDURATIONSECONDS, 
            @IMPRESSIONS, 
            @IMPRESSIONCALCULATIONMETHODCODE, 
            @GROUPS, 
            @SITEID;

        else if @SEGMENTTYPECODE = 8
          exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTMARKETINGLOCATION] 
            @TARGETSEGMENTID,
            @CHANGEAGENTID, 
            @QUERYVIEWCATALOGID, 
            @UNIQUENAME, 
            @DESCRIPTION, 
            @SEGMENTCATEGORYCODEID, 
            @CODE, 
            @CODEVALUEID, 
            @VENDORID,
            @LOCATIONCOUNTRYID, 
            @LOCATIONSTATEID, 
            @LOCATIONPOSTCODE, 
            @LOCATIONCITY, 
            @LOCATIONADDRESSBLOCK, 
            @IMPRESSIONS, 
            @IMPRESSIONCALCULATIONMETHODCODE,
            @GROUPS, 
            @SITEID;

        set @ID = @TARGETSEGMENTID;
      end
    else
      raiserror('BBERR_MKTSEGMENTTYPE_INVALIDSEGMENTTYPE', 13, 1);
  end try

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

return 0;