USP_DATALIST_LETTERCODES

Returns a list of revenue letters.
Parameters

Parameter	Parameter Type	Mode	Description
@INCLUDEINACTIVE	bit	IN	Include inactive letters
@RECORDTYPEID	uniqueidentifier	IN	Revenue type
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@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_LETTERCODES
(
    @INCLUDEINACTIVE bit = 0, 
    @RECORDTYPEID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
as
    set nocount on;

    select 
        LETTERCODE.ID,
        LETTERCODE.NAME as LETTERNAME,
        LETTERCODE.DESCRIPTION,
        LETTERCODE.WORDTEMPLATEFILENAME,
        IDSETREGISTER.NAME,
        BUSINESSPROCESSVIEW.CAPTION,
        case LETTERCODE.ISACTIVE when 1 then 'Active' else 'Inactive' end as STATUS,
        LETTERCODE.WORDTEMPLATEFILENAME,
        'WORDFILE',
        '802229a6-3104-4c55-b8fb-7def8827c75c' as VIEWFORMID,
        0 as TYPECODE,
        'Revenue' as TYPE,
        EXPORTDEFINITION.NAME,
        ( 
            select dbo.UDA_BUILDLIST(SITE.NAME)
            from dbo.UFN_SITEID_MAPFROM_LETTERCODEID(LETTERCODE.ID) LETTERCODESITES
            inner join dbo.SITE on SITE.ID=LETTERCODESITES.SITEID
        ) SITES
    from
        dbo.LETTERCODE
    left join
        dbo.IDSETREGISTER on LETTERCODE.IDSETREGISTERID = IDSETREGISTER.ID
    left join 
        dbo.QUERYVIEWCATALOG on LETTERCODE.QUERYVIEWID = QUERYVIEWCATALOG.ID
    left join
        dbo.BUSINESSPROCESSVIEW on QUERYVIEWCATALOG.ID = BUSINESSPROCESSVIEW.QUERYVIEWCATALOGID
    left join dbo.EXPORTDEFINITION on EXPORTDEFINITION.ID = LETTERCODE.EXPORTDEFINITIONID
    where
        ((@INCLUDEINACTIVE = 1)    or (@INCLUDEINACTIVE = 0 and LETTERCODE.ISACTIVE = 1)) and
        ((@RECORDTYPEID is null) or (@RECORDTYPEID=[IDSETREGISTER].[RECORDTYPEID]))
        and LETTERCODE.ISSYSTEM = 0
        and (
              select count(*) 
              from dbo.UFN_SITEID_MAPFROM_LETTERCODEID(LETTERCODE.ID) LETTERCODESITES 
              where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[LETTERCODESITES].[SITEID] or (SITEID is null and [LETTERCODESITES].[SITEID] is null)))
          ) > 0
          and    (
              @SITEFILTERMODE = 0
                  or exists(
                      select 1
                      from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                          inner join dbo.UFN_SITEID_MAPFROM_LETTERCODEID(LETTERCODE.ID) LETTERCODESITES  on LETTERCODESITES.SITEID = SITEFILTER.SITEID
                  )
          )
          and (
              -- hide marketing effort export record types

              LETTERCODE.EXPORTDEFINITIONID is null
              or [EXPORTDEFINITION].[RECORDTYPEID] not in (select [ID] from dbo.[RECORDTYPE] where [NAME] in ('Direct Marketing Effort Segment Member Export', 'Marketing Acknowledgement Segment Member Export', 'Membership Effort Segment Member Export', 'Sponsorship Effort Segment Member Export'))
          )

    union all

    select 
        TRIBUTELETTERCODE.ID,
        TRIBUTELETTERCODE.NAME as LETTERNAME,
        TRIBUTELETTERCODE.DESCRIPTION,
        TRIBUTELETTERCODE.WORDTEMPLATEFILENAME,
        null,
        BUSINESSPROCESSVIEW.CAPTION,
        case TRIBUTELETTERCODE.ISACTIVE when 1 then 'Active' else 'Inactive' end as STATUS,
        TRIBUTELETTERCODE.WORDTEMPLATEFILENAME,
        'WORDFILE',
        'bf6dba76-2a2b-4b00-b0c3-c421c9bb756c' as VIEWFORMID,
        1 as TYPECODE,
        'Tribute' as TYPE,
        EXPORTDEFINITION.NAME,
        ( 
            select dbo.UDA_BUILDLIST(SITE.NAME)
            from dbo.UFN_SITEID_MAPFROM_TRIBUTELETTERCODEID(TRIBUTELETTERCODE.ID) LETTERCODESITES
            inner join dbo.SITE on SITE.ID=LETTERCODESITES.SITEID
        ) SITES
    from
        dbo.TRIBUTELETTERCODE
    left join 
        dbo.QUERYVIEWCATALOG on TRIBUTELETTERCODE.QUERYVIEWID = QUERYVIEWCATALOG.ID
    left join
        dbo.BUSINESSPROCESSVIEW on QUERYVIEWCATALOG.ID = BUSINESSPROCESSVIEW.QUERYVIEWCATALOGID
    left join dbo.EXPORTDEFINITION on EXPORTDEFINITION.ID = TRIBUTELETTERCODE.EXPORTDEFINITIONID
    where
        (@INCLUDEINACTIVE = 1)
        or (@INCLUDEINACTIVE = 0 and TRIBUTELETTERCODE.ISACTIVE = 1)
        and (
              select count(*) 
              from dbo.UFN_SITEID_MAPFROM_TRIBUTELETTERCODEID(TRIBUTELETTERCODE.ID) LETTERCODESITES 
              where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[LETTERCODESITES].[SITEID] or (SITEID is null and [LETTERCODESITES].[SITEID] is null)))
          ) > 0
          and    (
              @SITEFILTERMODE = 0
                  or exists(
                      select 1
                      from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                          inner join dbo.UFN_SITEID_MAPFROM_TRIBUTELETTERCODEID(TRIBUTELETTERCODE.ID) LETTERCODESITES  on LETTERCODESITES.SITEID = SITEFILTER.SITEID
                  )
          )
          and (
              -- hide marketing effort export record types

              TRIBUTELETTERCODE.EXPORTDEFINITIONID is null
              or [EXPORTDEFINITION].[RECORDTYPEID] not in (select [ID] from dbo.[RECORDTYPE] where [NAME] in ('Direct Marketing Effort Segment Member Export', 'Marketing Acknowledgement Segment Member Export', 'Membership Effort Segment Member Export', 'Sponsorship Effort Segment Member Export'))
          )

    order by
        TYPE, LETTERNAME;