USP_DATALIST_DECEASEDFINDERSUBMITPROCESSOUTPUT

Returns a list of records for DeceasedRecordFinder submit process output.
Parameters

Parameter	Parameter Type	Mode	Description
@SELECTIONID	uniqueidentifier	IN	SelectionID
@ISPRIMARY	bit	IN	Is primary
@ADDRESSTYPECODEID	uniqueidentifier	IN	Address type
@INCLUDEADDRESSESDNM	bit	IN	Include addresses marked as Do Not Mail
@MAXROWS	int	IN	Input parameter indicating the maximum number of rows to return.
@OWNERID	uniqueidentifier	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_DECEASEDFINDERSUBMITPROCESSOUTPUT
(
  @SELECTIONID uniqueidentifier = null,
  @ISPRIMARY bit = 0,
  @ADDRESSTYPECODEID uniqueidentifier = null,
  @INCLUDEADDRESSESDNM bit = 0,
  @MAXROWS int,
  @OWNERID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null                    
)
as
set nocount on;

declare @BYPASSSECURITY bit;
set @BYPASSSECURITY = 0;
declare @BYPASSSITESECURITY bit;
declare @BPID uniqueidentifier;
set @BPID = '18F4FBE4-8AFB-4FFE-84E1-F1FC59948775';
set @CURRENTAPPUSERID = @OWNERID;
set @BYPASSSITESECURITY = 0;

if @OWNERID is not null
  begin
    select @BYPASSSECURITY = dbo.UFN_APPUSER_ISSYSADMIN(@OWNERID);

    if @BYPASSSECURITY = 0
      begin
        select @BYPASSSECURITY = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_NONRACROLE(@OWNERID, @BPID);
        select @BYPASSSITESECURITY = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_NONSITEROLE(@OWNERID, @BPID);
      end
    else
      set @BYPASSSITESECURITY = 1;
  end
else
  begin
    set @BYPASSSECURITY = 1;
    set @BYPASSSITESECURITY = 1;
  end

declare @SELECTION table (CONSTITUENTID uniqueidentifier, ADDRESSID uniqueidentifier, LOOKUPID nvarchar(100), CONSTITUENTNAME nvarchar(154), ADDRESS nvarchar(150), CITY nvarchar(50), STATE nvarchar(100), POSTCODE nvarchar(12));

if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
  begin
    if @SELECTIONID is not null
      begin
        insert into @SELECTION
        (
          CONSTITUENTID, 
          ADDRESSID, 
          LOOKUPID, 
          CONSTITUENTNAME, 
          ADDRESS, 
          CITY, 
          STATE, 
          POSTCODE
        )
        (
          select top(@MAXROWS) 
            CONSTITUENTID,
            ADDRESS.ID as ADDRESSID,
            CONSTITUENT.LOOKUPID as LOOKUPID,
            NF.NAME as CONSTITUENTNAME,
            ADDRESS.ADDRESSBLOCK as ADDRESS,
            ADDRESS.CITY as CITY,
            dbo.UFN_STATE_GETDESCRIPTION(ADDRESS.STATEID) as STATE,
            ADDRESS.POSTCODE as POSTCODE
          from 
            dbo.CONSTITUENT
          inner join 
            dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION 
          on 
            CONSTITUENT.ID = SELECTION.ID and @SELECTIONID is not null
          inner join 
            dbo.ADDRESS 
          on 
            ADDRESS.CONSTITUENTID = CONSTITUENT.ID
          inner join 
            dbo.COUNTRY 
          on    
            COUNTRY.ID = ADDRESS.COUNTRYID and COUNTRY.USEFORCOAUPDATE = 1
          left join 
            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS 
          on 
            CONSTITUENT.ID = CONSTIT_RACS.ID
          outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
          where 
            (CONSTITUENT.ISORGANIZATION = 0) 
          and
            (CONSTITUENT.ISGROUP = 0) 
          and
            (ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0) 
          and
            (ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null) 
          and
            (ADDRESS.DONOTMAIL = @INCLUDEADDRESSESDNM or ADDRESS.DONOTMAIL = 0) 
          and
            (CONSTITUENT.ID not in (select ID from dbo.DECEASEDCONSTITUENT))
          and(
            ADDRESS.ADDRESSBLOCK <> ''
            and
            ADDRESS.STATEID is not null
            and
            ADDRESS.CITY <> ''
          )
          and(
            @BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
          and (
            @BYPASSSITESECURITY = 1 or
              exists(
                select 1 from dbo.CONSTITUENT C
                left join dbo.CONSTITUENTSITE on CONSTITUENTSITE.CONSTITUENTID = C.ID
                where
                  C.ID = CONSTITUENT.ID
                and 
                  (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[CONSTITUENTSITE].[SITEID] or (SITEID is null and [CONSTITUENTSITE].[SITEID] is null)))
              )
          )
        );
      end
    else
      begin
        insert into @SELECTION
        (
          CONSTITUENTID, 
          ADDRESSID, 
          LOOKUPID, 
          CONSTITUENTNAME, 
          ADDRESS, 
          CITY, 
          STATE, 
          POSTCODE
        )
        (
          select top(@MAXROWS) 
            CONSTITUENTID,
            ADDRESS.ID as ADDRESSID,
            CONSTITUENT.LOOKUPID as LOOKUPID,
            NF.NAME as CONSTITUENTNAME,
            ADDRESS.ADDRESSBLOCK as ADDRESS,
            ADDRESS.CITY as CITY,
            dbo.UFN_STATE_GETDESCRIPTION(ADDRESS.STATEID) as STATE,
            ADDRESS.POSTCODE as POSTCODE
          from 
            dbo.CONSTITUENT
          inner join 
            dbo.ADDRESS 
          on 
            ADDRESS.CONSTITUENTID = CONSTITUENT.ID
          inner join 
            dbo.COUNTRY 
          on 
            COUNTRY.ID = ADDRESS.COUNTRYID and COUNTRY.USEFORCOAUPDATE = 1
          left join 
            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS 
          on 
            CONSTITUENT.ID = CONSTIT_RACS.ID
          outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
          where 
            (CONSTITUENT.ISORGANIZATION = 0) 
          and
            (CONSTITUENT.ISGROUP = 0) 
          and
            (ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0) 
          and
            (ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null) 
          and
            (ADDRESS.DONOTMAIL = @INCLUDEADDRESSESDNM or ADDRESS.DONOTMAIL = 0)
          and
            (CONSTITUENT.ID not in (select ID from dbo.DECEASEDCONSTITUENT))
          and(
            ADDRESS.ADDRESSBLOCK <> ''
            and
            ADDRESS.STATEID is not null
            and
            ADDRESS.CITY <> ''
          )                                        
          and(
            @BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
          and (
            @BYPASSSITESECURITY = 1 or
              exists(
                select 1 from dbo.CONSTITUENT C
                left join dbo.CONSTITUENTSITE on CONSTITUENTSITE.CONSTITUENTID = C.ID
                where
                  C.ID = CONSTITUENT.ID
                and 
                  (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[CONSTITUENTSITE].[SITEID] or (SITEID is null and [CONSTITUENTSITE].[SITEID] is null)))
              )
          )
        );
      end
  end
else
  begin
    if @SELECTIONID is not null
      begin
        insert into @SELECTION
          (
            CONSTITUENTID,
            ADDRESSID, 
            LOOKUPID, 
            CONSTITUENTNAME,
            ADDRESS, 
            CITY, 
            STATE, 
            POSTCODE
          )
          (
            select top(@MAXROWS) 
              CONSTITUENTID,
              ADDRESS.ID as ADDRESSID,
              CONSTITUENT.LOOKUPID as LOOKUPID,
              NF.NAME as CONSTITUENTNAME,
              ADDRESS.ADDRESSBLOCK as ADDRESS,
              ADDRESS.CITY as CITY,
              dbo.UFN_STATE_GETDESCRIPTION(ADDRESS.STATEID) as STATE,
              ADDRESS.POSTCODE as POSTCODE
            from 
              dbo.CONSTITUENT
            inner join
              dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION 
            on 
              CONSTITUENT.ID = SELECTION.ID and @SELECTIONID is not null
            inner join 
              dbo.ADDRESS 
            on 
              ADDRESS.CONSTITUENTID = CONSTITUENT.ID
            inner join 
              dbo.COUNTRY 
            on 
              COUNTRY.ID = ADDRESS.COUNTRYID and COUNTRY.USEFORCOAUPDATE = 1
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
            where 
              (CONSTITUENT.ISORGANIZATION = 0)
            and
              (CONSTITUENT.ISGROUP = 0) 
            and
              (ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0) 
            and
              (ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null) 
            and
              (ADDRESS.DONOTMAIL = @INCLUDEADDRESSESDNM or ADDRESS.DONOTMAIL = 0) 
            and
              (CONSTITUENT.ID not in (select ID from dbo.DECEASEDCONSTITUENT))
            and(
              ADDRESS.ADDRESSBLOCK <> ''
              and
              ADDRESS.STATEID is not null
              and
              ADDRESS.CITY <> ''
            )
          );
      end
    else
      begin
        insert into @SELECTION
        (
          CONSTITUENTID, 
          ADDRESSID, 
          LOOKUPID, 
          CONSTITUENTNAME, 
          ADDRESS, 
          CITY, 
          STATE, 
          POSTCODE
        )
        (
          select top(@MAXROWS) 
            CONSTITUENTID,
            ADDRESS.ID as ADDRESSID,
            CONSTITUENT.LOOKUPID as LOOKUPID,
            NF.NAME as CONSTITUENTNAME,
            ADDRESS.ADDRESSBLOCK as ADDRESS,
            ADDRESS.CITY as CITY,
            dbo.UFN_STATE_GETDESCRIPTION(ADDRESS.STATEID) as STATE,
            ADDRESS.POSTCODE as POSTCODE
          from 
            dbo.CONSTITUENT
          inner join 
            dbo.ADDRESS 
          on 
            ADDRESS.CONSTITUENTID = CONSTITUENT.ID
          inner join 
            dbo.COUNTRY 
          on 
            COUNTRY.ID = ADDRESS.COUNTRYID and COUNTRY.USEFORCOAUPDATE = 1
          outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
          where 
            (CONSTITUENT.ISORGANIZATION = 0) 
          and
            (CONSTITUENT.ISGROUP = 0) 
          and
            (ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0) 
          and
            (ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null) 
          and
            (ADDRESS.DONOTMAIL = @INCLUDEADDRESSESDNM or ADDRESS.DONOTMAIL = 0) 
          and
            (CONSTITUENT.ID not in (select ID from dbo.DECEASEDCONSTITUENT))
          and(
            ADDRESS.ADDRESSBLOCK <> ''
            and
            ADDRESS.STATEID is not null
            and
            ADDRESS.CITY <> ''
          )
        );
      end
  end

begin
  select
    CONSTITUENTID,
    ADDRESSID,
    LOOKUPID,
    CONSTITUENTNAME,
    ADDRESS,
    CITY,
    STATE,
    POSTCODE
  from 
    @SELECTION
end