USP_DATALIST_MKTCONSTITUENTFILEIMPORT

Display information about a segmented house file import.

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.
@STATUSCODE	tinyint	IN	Status
@DATEFROM	datetime	IN	Start date
@DATETO	datetime	IN	End date

Definition

Copy



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

  select 
    [MKTCONSTITUENTFILEIMPORTPROCESS].[ID] [ID],
    [MKTSEGMENTATION].[ID] [SEGMENTATIONID],
    [MKTSEGMENTATION].[NAME] [MAILINGNAME],
    [MKTCONSTITUENTFILECATEGORYCODE].[DESCRIPTION] [CATEGORY],
    [MKTCONSTITUENTFILEIMPORTPROCESS].[DATEADDED] [IMPORTDATE],
    [MKTCONSTITUENTFILEIMPORTPROCESS].[QUANTITY] [QUANTITY],
    --dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_BUILDRANGE](FN.[MIN], FN.[MAX], FN.[FIXEDWIDTH], FN.[CHECKDIGIT]) [RANGE],

    [MKTCONSTITUENTFILEIMPORTPROCESS].[STATUSCODE] [STATUSCODE],
    [MKTCONSTITUENTFILEIMPORTPROCESS].[STATUS],
    dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTSEGMENTATION].[SITEID]) as [MAILINGSITEALLOWED],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTSEGMENTATION].[SITEID]) as [SITE]
  from dbo.[MKTCONSTITUENTFILEIMPORTPROCESS]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTCONSTITUENTFILEIMPORTPROCESS].[SEGMENTATIONID]
  left join dbo.[MKTCONSTITUENTFILECATEGORYCODE] on [MKTCONSTITUENTFILECATEGORYCODE].[ID] = [MKTCONSTITUENTFILEIMPORTPROCESS].[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)))
    and (@STATUSCODE is null or [MKTCONSTITUENTFILEIMPORTPROCESS].[STATUSCODE] = @STATUSCODE)
    and (@DATEFROM is null or [MKTCONSTITUENTFILEIMPORTPROCESS].[DATEADDED] >= @DATEFROM)
    and (@DATETO is null or [MKTCONSTITUENTFILEIMPORTPROCESS].[DATEADDED] <= @DATETO)
  order by [IMPORTDATE] desc;

  return 0;