USP_DATALIST_APPEALMAILINGSELECTIONSBYGROUPNAME

Retrieves the selections for the given record type and group name and which are associated with a current appeal mailing.
Parameters

Parameter	Parameter Type	Mode	Description
@RECORDTYPEID	uniqueidentifier	IN	Record type ID
@GROUPNAME	nvarchar(50)	IN	Group name
Definition

Copy


CREATE procedure dbo.USP_DATALIST_APPEALMAILINGSELECTIONSBYGROUPNAME
(
    @RECORDTYPEID uniqueidentifier,
    @GROUPNAME nvarchar(50)
)
as
set nocount on;

select
    IDSETREGISTER.ID,
    IDSETREGISTER.NAME
from 
    IDSETREGISTER
inner join 
    APPEALMAILINGSETUPSELECTION on APPEALMAILINGSETUPSELECTION.SELECTIONID = IDSETREGISTER.ID
inner join
    APPEALMAILINGSETUP on APPEALMAILINGSETUP.ID = APPEALMAILINGSETUPSELECTION.APPEALMAILINGSETUPID
inner join
    MKTSEGMENTATION on MKTSEGMENTATION.ID = APPEALMAILINGSETUP.ID
left join 
    dbo.SQLFUNCTIONCATALOG on SQLFUNCTIONCATALOG.FUNCTIONNAME = IDSETREGISTER.DBOBJECTNAME
where
    IDSETREGISTER.ACTIVE = 1
    and IDSETREGISTER.RECORDTYPEID = @RECORDTYPEID
    and IDSETREGISTER.GROUPNAME = @GROUPNAME
    and (
        (SQLFUNCTIONCATALOG.ID is null)
        or dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED (
            SQLFUNCTIONCATALOG.SQLFUNCTIONSPECXML.query (
                'declare namespace common="bb_appfx_commontypes";
                /*/common:InstalledProductList'
            )
        ) = 1
    ) 
    -- Appeal mailings for this year and next year
    and (MKTSEGMENTATION.MAILDATE is not null) and
    (year(MKTSEGMENTATION.MAILDATE) between year(getdate()) and (year(getdate()) + 1))

union

select
    IDSETREGISTER.ID,
    IDSETREGISTER.NAME
from 
    IDSETREGISTER
inner join 
    MKTSEGMENTSELECTION on MKTSEGMENTSELECTION.SELECTIONID = IDSETREGISTER.ID
inner join 
    MKTSEGMENT on MKTSEGMENT.ID = MKTSEGMENTSELECTION.SEGMENTID
inner join 
    MKTSEGMENTATIONSEGMENT on MKTSEGMENTATIONSEGMENT.SEGMENTID = MKTSEGMENT.ID
inner join
    MKTSEGMENTATION on MKTSEGMENTATION.ID = MKTSEGMENTATIONSEGMENT.SEGMENTATIONID
inner join
    APPEALMAILING on APPEALMAILING.ID = MKTSEGMENTATION.ID
left join 
    dbo.SQLFUNCTIONCATALOG on SQLFUNCTIONCATALOG.FUNCTIONNAME = IDSETREGISTER.DBOBJECTNAME
where
    IDSETREGISTER.ACTIVE = 1
    and IDSETREGISTER.ISSYSTEM = 0
    and IDSETREGISTER.RECORDTYPEID = @RECORDTYPEID
    and IDSETREGISTER.GROUPNAME = @GROUPNAME
    and (
        (SQLFUNCTIONCATALOG.ID is null)
        or dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED (
            SQLFUNCTIONCATALOG.SQLFUNCTIONSPECXML.query (
                'declare namespace common="bb_appfx_commontypes";
                /*/common:InstalledProductList'
            )
        ) = 1
    ) 
    -- Appeal mailings for this year and next year

    and (MKTSEGMENTATION.MAILDATE is not null) and
    (year(MKTSEGMENTATION.MAILDATE) between year(getdate()) and (year(getdate()) + 1))

order by NAME