USP_DATALIST_PLANNEDGIFTLETTERCODES

Returns a list of planned gift letters.

Parameters

Parameter	Parameter Type	Mode	Description
@INCLUDEINACTIVE	bit	IN	Include inactive letters
@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_PLANNEDGIFTLETTERCODES
        (
          @INCLUDEINACTIVE bit = 0,
          @SITEFILTERMODE tinyint = 0,
          @SITESSELECTED xml = null,
          @CURRENTAPPUSERID uniqueidentifier = null,
          @SECURITYFEATUREID uniqueidentifier = null,
          @SECURITYFEATURETYPE tinyint = null
        )
        as
          set nocount on;

          select 
            PLANNEDGIFTLETTERCODE.ID,
            PLANNEDGIFTLETTERCODE.NAME,
            PLANNEDGIFTLETTERCODE.DESCRIPTION,
            PLANNEDGIFTLETTERCODE.WORDTEMPLATEFILENAME,
            IDSETREGISTER.NAME,
            BPVIEW.CAPTION,
            case PLANNEDGIFTLETTERCODE.ISACTIVE when 1 then 'Active' else 'Inactive' end as STATUS,
            PLANNEDGIFTLETTERCODE.WORDTEMPLATEFILENAME,
            'WORDFILE',
            '4577A630-67EA-4AC1-B849-E82527FBFEF2' as VIEWFORMID,
            EXPORTDEFINITION.NAME,
            (
              select dbo.[UDA_BUILDLIST]([SITE].[NAME])
              from dbo.[UFN_SITEID_MAPFROM_PLANNEDGIFTLETTERCODEID]([PLANNEDGIFTLETTERCODE].[ID]) [PLANNEDGIFTLETTERCODESITES]
              inner join dbo.[SITE] on [SITE].[ID]=[PLANNEDGIFTLETTERCODESITES].[SITEID]
            ) [SITES]
          from
            dbo.PLANNEDGIFTLETTERCODE
          left join
            dbo.IDSETREGISTER on PLANNEDGIFTLETTERCODE.IDSETREGISTERID = IDSETREGISTER.ID
          left join 
            dbo.QUERYVIEWCATALOG on PLANNEDGIFTLETTERCODE.QUERYVIEWID = QUERYVIEWCATALOG.ID
          left join
            dbo.BUSINESSPROCESSVIEW BPVIEW on QUERYVIEWCATALOG.ID = BPVIEW.QUERYVIEWCATALOGID
          left join 
            dbo.EXPORTDEFINITION on EXPORTDEFINITION.ID = PLANNEDGIFTLETTERCODE.EXPORTDEFINITIONID
          where
            ((@INCLUDEINACTIVE = 1)
            or (@INCLUDEINACTIVE = 0 and PLANNEDGIFTLETTERCODE.ISACTIVE = 1))
            and (
                select count(1) 
                from dbo.[UFN_SITEID_MAPFROM_PLANNEDGIFTLETTERCODEID]([PLANNEDGIFTLETTERCODE].[ID]) [PLANNEDGIFTLETTERCODESITES]
                where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PLANNEDGIFTLETTERCODESITES].[SITEID] or (SITEID is null and [PLANNEDGIFTLETTERCODESITES].[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_PLANNEDGIFTLETTERCODEID]([PLANNEDGIFTLETTERCODE].[ID]) [PLANNEDGIFTLETTERCODESITES] on [PLANNEDGIFTLETTERCODESITES].[SITEID] = [SITEFILTER].[SITEID]
                  )
              )
          order by
            PLANNEDGIFTLETTERCODE.NAME;