USP_DATALIST_MKTFINDERFILE

Display information about a finder file.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN	Sites selected
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTFINDERFILE]
(
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
  set nocount on;

  select 
    [MKTFINDERFILEIMPORTPROCESS].[ID],
    [MKTSEGMENTATION].[ID] as [SEGMENTATIONID],
    [MKTSEGMENTATION].[NAME] as [MAILINGNAME],
    [CONSTITUENT].[ID] as [VENDORID],
    [CONSTITUENT].[KEYNAME] as [VENDORNAME],
    [MKTFINDERFILECATEGORYCODE].[DESCRIPTION] as [CATEGORY],
    [MKTFINDERFILEIMPORTPROCESS].[ORDERDATE],
    [MKTFINDERFILEIMPORTPROCESS].[EXPIRATIONDATE],
    [MKTSEGMENTATION].[MAILDATE],
    [MKTFINDERFILEIMPORTPROCESS].[DATEADDED] as [IMPORTDATE],
    [MKTFINDERFILEIMPORTPROCESS].[QUANTITY],
    dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_BUILDRANGE]([MKTSEGMENTATIONFINDERNUMBER].[MIN], [MKTSEGMENTATIONFINDERNUMBER].[MAX], [MKTSEGMENTATIONFINDERNUMBER].[FIXEDWIDTH], [MKTSEGMENTATIONFINDERNUMBER].[CHECKDIGIT]) as [RANGE],
    [MKTFINDERFILEIMPORTPROCESS].[STATUSCODE],
    [MKTFINDERFILEIMPORTPROCESS].[STATUS],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTSEGMENTATION].[SITEID]) as [SITE]
  from dbo.[MKTFINDERFILEIMPORTPROCESS]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTFINDERFILEIMPORTPROCESS].[SEGMENTATIONID]
  inner join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTFINDERFILEIMPORTPROCESS].[VENDORID]
  inner join dbo.[MKTSEGMENTATIONFINDERNUMBER] on [MKTSEGMENTATIONFINDERNUMBER].[SEGMENTATIONID] = [MKTFINDERFILEIMPORTPROCESS].[SEGMENTATIONID] and [MKTSEGMENTATIONFINDERNUMBER].[VENDORID] = [MKTFINDERFILEIMPORTPROCESS].[VENDORID]
  left join dbo.[MKTFINDERFILECATEGORYCODE] on [MKTFINDERFILECATEGORYCODE].[ID] = [MKTFINDERFILEIMPORTPROCESS].[CATEGORYCODEID]
  where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTSEGMENTATION].[SITEID] or (SITEID is null and [MKTSEGMENTATION].[SITEID] is null)))
  and (@SITEFILTERMODE = 0 or [MKTSEGMENTATION].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
  order by [MKTFINDERFILEIMPORTPROCESS].[DATEADDED] asc;

  return 0;