USP_MKTSEGMENTLIST_ADD_SAVE

Adds a list based segment.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(255)	IN
@SEGMENTCATEGORYCODEID	uniqueidentifier	IN
@CODE	nvarchar(10)	IN
@ORDERDATE	datetime	IN
@EXPIRATIONDATE	datetime	IN
@NUMBEROFCONTACTS	smallint	IN
@LISTID	uniqueidentifier	IN
@LISTLAYOUTID	uniqueidentifier	IN
@FILENAME	nvarchar(255)	IN
@RENTALQUANTITY	int	IN
@RENTALCOSTADJUSTMENT	money	IN
@RENTALCOSTBASISCODE	tinyint	IN
@EXCHANGEQUANTITY	int	IN
@EXCHANGECOSTADJUSTMENT	money	IN
@EXCHANGECOSTBASISCODE	tinyint	IN
@GROUPS	xml	IN
@TYPECODE	tinyint	IN
@CODEVALUEID	uniqueidentifier	IN
@SITEID	uniqueidentifier	IN
Definition

Copy


CREATE procedure dbo.[USP_MKTSEGMENTLIST_ADD_SAVE]
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,    
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255) = '',
  @SEGMENTCATEGORYCODEID uniqueidentifier = null,
  @CODE nvarchar(10) = '',
  @ORDERDATE datetime = null,
  @EXPIRATIONDATE datetime = null,
  @NUMBEROFCONTACTS smallint = 1,
  @LISTID uniqueidentifier,
  @LISTLAYOUTID uniqueidentifier = null,
  @FILENAME nvarchar(255) = '',
  @RENTALQUANTITY int = 0,
  @RENTALCOSTADJUSTMENT money = 0,
  @RENTALCOSTBASISCODE tinyint = 1, -- per thousand

  @EXCHANGEQUANTITY int = 0,
  @EXCHANGECOSTADJUSTMENT money = 0,
  @EXCHANGECOSTBASISCODE tinyint = 1, -- per thousand

  @GROUPS xml = null,
  @TYPECODE tinyint = 0, --0=Imported, 1=VendorManaged

  @CODEVALUEID uniqueidentifier = null,
  @SITEID uniqueidentifier = null
)
as
  set nocount on;

  declare @SEGMENTLISTID uniqueidentifier;
  declare @RECORDSOURCEID uniqueidentifier;
  declare @STATUSCODE tinyint;
  declare @CURRENTDATE datetime;
  declare @BASECURRENCYID uniqueidentifier;
  declare @CURRENCYEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONRENTALCOSTADJUSTMENT money;
  declare @ORGANIZATIONEXCHANGECOSTADJUSTMENT money;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;

  begin try
    if @ID is null
      set @ID = newid();

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

    set @CURRENTDATE = getdate();
    set @SEGMENTLISTID = newid();
    set @STATUSCODE = (case @TYPECODE
                       when 0 then 0  --Imported, PendingImport

                       when 1 then 3  --VendorManged, Active

                       end);

    select
      @RECORDSOURCEID = [RECORDSOURCEID]
    from dbo.[MKTLIST]
    where [ID] = @LISTID;

        select
            @BASECURRENCYID = [MKTLIST].[BASECURRENCYID]
        from
            dbo.[MKTLIST]
        where
            [MKTLIST].[ID] = @LISTID;

    --Save the segment, using segment type 2 (list)...

    insert into dbo.[MKTSEGMENT] (
      [ID],
      [NAME],
      [DESCRIPTION],
      [SEGMENTCATEGORYCODEID],
      [CODE],
      [PARTDEFINITIONVALUESID],
      [QUERYVIEWCATALOGID],
      [SEGMENTTYPECODE],
      [SITEID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [BASECURRENCYID]
    ) values (
      @ID,
      @NAME,
      @DESCRIPTION,
      @SEGMENTCATEGORYCODEID,
      @CODE,
      @CODEVALUEID,
      @RECORDSOURCEID,
      2,
      @SITEID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE,
      @BASECURRENCYID
    );

        set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

        if (@ORGANIZATIONCURRENCYID = @BASECURRENCYID)
        begin
            set @ORGANIZATIONRENTALCOSTADJUSTMENT = @RENTALCOSTADJUSTMENT;
      set @ORGANIZATIONEXCHANGECOSTADJUSTMENT = @EXCHANGECOSTADJUSTMENT;
        end
        else
        begin
            set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);
            set @ORGANIZATIONRENTALCOSTADJUSTMENT = dbo.[UFN_CURRENCY_CONVERT](@RENTALCOSTADJUSTMENT, @CURRENCYEXCHANGERATEID);
      set @ORGANIZATIONEXCHANGECOSTADJUSTMENT = dbo.[UFN_CURRENCY_CONVERT](@EXCHANGECOSTADJUSTMENT, @CURRENCYEXCHANGERATEID);
        end

    insert into dbo.[MKTSEGMENTLIST] (
      [ID],
      [SEGMENTID],
      [LISTID],
      [TYPECODE],
      [STATUSCODE],
      [ORDERDATE],
      [EXPIRATIONDATE],
      [NUMBEROFCONTACTS],
      [LISTLAYOUTID],
      [FILENAME],
      [RENTALQUANTITY],
      [RENTALCOSTADJUSTMENT],
      [RENTALCOSTBASISCODE],
      [EXCHANGEQUANTITY],
      [EXCHANGECOSTADJUSTMENT],
      [EXCHANGECOSTBASISCODE],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [BASECURRENCYID],
      [ORGANIZATIONRENTALCOSTADJUSTMENT],
      [ORGANIZATIONEXCHANGECOSTADJUSTMENT],
      [CURRENCYEXCHANGERATEID]
    ) values (
      @SEGMENTLISTID,
      @ID,
      @LISTID,
      @TYPECODE,
      @STATUSCODE,
      @ORDERDATE,
      @EXPIRATIONDATE,
      @NUMBEROFCONTACTS,
      @LISTLAYOUTID,
      @FILENAME,
      @RENTALQUANTITY,
      @RENTALCOSTADJUSTMENT,
      @RENTALCOSTBASISCODE,
      @EXCHANGEQUANTITY,
      @EXCHANGECOSTADJUSTMENT,
      @EXCHANGECOSTBASISCODE,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE,
      @BASECURRENCYID,
      @ORGANIZATIONRENTALCOSTADJUSTMENT,
      @ORGANIZATIONEXCHANGECOSTADJUSTMENT,
      @CURRENCYEXCHANGERATEID
    );

    --Update the segment to hold the current list segment record ID...

    --Need to set the CODE field again here so the check constraint gets validated now that we have set the CURRENTSEGMENTLISTID field.

    update dbo.[MKTSEGMENT] set
      [CURRENTSEGMENTLISTID] = @SEGMENTLISTID,
      [CODE] = @CODE,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

    --Save all the groups...

    exec dbo.[USP_MKTSEGMENT_GETGROUPS_ADDFROMXML] @ID, @GROUPS, @CHANGEAGENTID, @CURRENTDATE;

    --For imported list segments only, add an import business process parameter set...

    if @TYPECODE = 0  --Imported

      exec dbo.[USP_MKTSEGMENTLISTIMPORTPROCESS_CREATE] null, @SEGMENTLISTID, @CHANGEAGENTID;
  end try

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

  return 0;