USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTGENERATE_PRELOAD

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTGENERATE_PRELOAD]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @QUERYVIEWCATALOGID uniqueidentifier,
  @RECORDTYPEID uniqueidentifier = null output,
  @SMARTQUERIESEXIST bit = null output,
  @CODEISREQUIRED bit = null output,
  @SEGMENTPARTDEFINITIONID uniqueidentifier = null output,
  @SEGMENTTYPECODE tinyint = null output,
  @RECORDSOURCEID uniqueidentifier = null output,
  @AUTOINCREMENTCODE bit = null output,
  @SITEID uniqueidentifier = null output,
  @SITEREQUIRED bit = null output,
  @RECORDTYPE nvarchar(50) = null output,
  @SELECTIONTYPEMASK int = null output
)
as
  set nocount on;

  set @SMARTQUERIESEXIST = dbo.[UFN_MKTSELECTION_SMARTQUERIESEXIST](1);

  select
    @RECORDTYPEID = [QUERYVIEWCATALOG].[RECORDTYPEID],
    @RECORDTYPE = [RECORDTYPE].[NAME]
  from dbo.[QUERYVIEWCATALOG]
  inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [QUERYVIEWCATALOG].[RECORDTYPEID]
  where [QUERYVIEWCATALOG].[ID] = @QUERYVIEWCATALOGID;

  set @SITEID = dbo.[UFN_APPUSER_DEFAULTSITEFORUSER](@CURRENTAPPUSERID);
  set @SITEREQUIRED = dbo.[UFN_SITEREQUIREDFORUSERONFEATURE](@CURRENTAPPUSERID, 'EEBBFE86-27FB-4DA7-88D8-15508F6FC16C', 1);

  set @CODEISREQUIRED = (case when exists(
                            select top 1 1
                            from dbo.[MKTSEGMENTLIST]
                            inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[CURRENTSEGMENTLISTID] = [MKTSEGMENTLIST].[ID]
                            where [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID)
                         then 1 else 0 end);

  --Get the segment part definition ID for the segment source code part...

  -- See whether or not the segment source code type is set to auto-increment.

  select 
    @SEGMENTPARTDEFINITIONID = [ID],
    @AUTOINCREMENTCODE = [AUTOINCREMENTVALUE]
  from dbo.[MKTSOURCECODEPARTDEFINITION] where [ITEMTYPECODE] = 1;

  /***********************************************************/
  /* Figure out what type of segments we are dealing with... */
  /***********************************************************/
  --Check if we have a record source...

  select top 1
    @RECORDSOURCEID = [MKTRECORDSOURCE].[ID],
    @SEGMENTTYPECODE = 1,
    @SELECTIONTYPEMASK = 1
  from dbo.[MKTRECORDSOURCE]
  inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
  left join dbo.[RECORDTYPECOMPATIBILITYMAP] on [RECORDTYPECOMPATIBILITYMAP].[RECORDTYPE1ID] = [QUERYVIEWCATALOG].[RECORDTYPEID]
  where ([QUERYVIEWCATALOG].[RECORDTYPEID] = @RECORDTYPEID or [RECORDTYPECOMPATIBILITYMAP].[RECORDTYPE2ID] = @RECORDTYPEID)
  and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1;

  if @RECORDSOURCEID is null
    --Check if we have a list...

    select
      @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
      @SEGMENTTYPECODE = 2,
      @SELECTIONTYPEMASK = 1
    from dbo.[MKTSEGMENTLIST]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[CURRENTSEGMENTLISTID] = [MKTSEGMENTLIST].[ID]
    where [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID;

  if @RECORDSOURCEID is null
    --Check if we have a gift record source...

    select top 1
      @RECORDSOURCEID = [MKTGIFTRECORDSOURCE].[ID],
      @SEGMENTTYPECODE = 3,
      @SELECTIONTYPEMASK = 2
    from dbo.[MKTGIFTRECORDSOURCE]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID]
    left join dbo.[RECORDTYPECOMPATIBILITYMAP] on [RECORDTYPECOMPATIBILITYMAP].[RECORDTYPE1ID] = [QUERYVIEWCATALOG].[RECORDTYPEID]
    where ([QUERYVIEWCATALOG].[RECORDTYPEID] = @RECORDTYPEID or [RECORDTYPECOMPATIBILITYMAP].[RECORDTYPE2ID] = @RECORDTYPEID)
    and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTGIFTRECORDSOURCE].[ID]) = 1;

  if @RECORDSOURCEID is null
    --Check if we have a membership record source...

    select top 1
      @RECORDSOURCEID = [MKTMEMBERSHIPRECORDSOURCE].[ID],
      @SEGMENTTYPECODE = 4,
      @SELECTIONTYPEMASK = 4
    from dbo.[MKTMEMBERSHIPRECORDSOURCE]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTMEMBERSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
    left join dbo.[RECORDTYPECOMPATIBILITYMAP] on [RECORDTYPECOMPATIBILITYMAP].[RECORDTYPE1ID] = [QUERYVIEWCATALOG].[RECORDTYPEID]
    where ([QUERYVIEWCATALOG].[RECORDTYPEID] = @RECORDTYPEID or [RECORDTYPECOMPATIBILITYMAP].[RECORDTYPE2ID] = @RECORDTYPEID)
    and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTMEMBERSHIPRECORDSOURCE].[ID]) = 1;

  if @RECORDSOURCEID is null
    --Check if we have a sponsorship record source...

    select top 1
      @RECORDSOURCEID = [MKTSPONSORSHIPRECORDSOURCE].[ID],
      @SEGMENTTYPECODE = 5,
      @SELECTIONTYPEMASK = 8
    from dbo.[MKTSPONSORSHIPRECORDSOURCE]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSPONSORSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
    left join dbo.[RECORDTYPECOMPATIBILITYMAP] on [RECORDTYPECOMPATIBILITYMAP].[RECORDTYPE1ID] = [QUERYVIEWCATALOG].[RECORDTYPEID]
    where ([QUERYVIEWCATALOG].[RECORDTYPEID] = @RECORDTYPEID or [RECORDTYPECOMPATIBILITYMAP].[RECORDTYPE2ID] = @RECORDTYPEID)
    and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTSPONSORSHIPRECORDSOURCE].[ID]) = 1;

  if @RECORDSOURCEID is null
    --Check if we have a consolidated list...

    select
      @RECORDSOURCEID = [ID],
      @SEGMENTTYPECODE = 1,
      @SELECTIONTYPEMASK = 1
    from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC]
    where [CONSOLIDATEDQUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID
    and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([ID]) = 1;

  return 0;