USP_DATALIST_ADDRESSVALIDATIONPROCESSOUTPUT

Preview the data to be validated through the address validation process.
Parameters

Parameter	Parameter Type	Mode	Description
@SELECTIONID	uniqueidentifier	IN	SelectionID
@INCLUDECONSTITUENTDNM	bit	IN	Include constituents marked as Do Not Mail
@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_ADDRESSVALIDATIONPROCESSOUTPUT
(
    @SELECTIONID uniqueidentifier = null,
    @INCLUDECONSTITUENTDNM bit = 0,
    @ISPRIMARY bit = 0,
    @ADDRESSTYPECODEID uniqueidentifier = null,
    @INCLUDEADDRESSESDNM bit = 0,
    @MAXROWS int,
    @OWNERID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
as
    set nocount on;

    declare @BYPASSSECURITY bit;
    declare @BPID uniqueidentifier;
    declare @BYPASSSITESECURITY bit;    
    set @BYPASSSECURITY = 0;
    set @BPID = '3e0ecea2-194e-4260-a9d6-4fc21de8867c';
    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

    if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
    begin
        if @SELECTIONID is not null
        begin
            select top(@MAXROWS) CONSTITUENTID,
                    ADDRESS.ID as ADDRESSID,
                    CONSTITUENT.LOOKUPID as LOOKUPID,
                    CONSTITUENT.NAME as CONSTITUENTNAME,
                    case when CONSTITUENT.ISORGANIZATION = 1 then CONSTITUENT.NAME end as ORGNAME,
                    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.COUNTRYVALIDATIONINFO on COUNTRYVALIDATIONINFO.ID = ADDRESS.COUNTRYID and COUNTRYVALIDATIONINFO.ALLOWVALIDATION = 1
            left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on CONSTITUENT.ID = CONSTIT_RACS.ID
            where (CONSTITUENT.DONOTMAIL = @INCLUDECONSTITUENTDNM or CONSTITUENT.DONOTMAIL = 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
                (@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
            select top(@MAXROWS) CONSTITUENTID,
                        ADDRESS.ID as ADDRESSID,
                        CONSTITUENT.LOOKUPID as LOOKUPID,
                        CONSTITUENT.NAME as CONSTITUENTNAME,
                        case when CONSTITUENT.ISORGANIZATION = 1 then CONSTITUENT.NAME end as ORGNAME,
                        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.COUNTRYVALIDATIONINFO on COUNTRYVALIDATIONINFO.ID = ADDRESS.COUNTRYID and COUNTRYVALIDATIONINFO.ALLOWVALIDATION = 1
                left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on CONSTITUENT.ID = CONSTIT_RACS.ID
                where (CONSTITUENT.DONOTMAIL = @INCLUDECONSTITUENTDNM or CONSTITUENT.DONOTMAIL = 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
                    (@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
            select top(@MAXROWS) CONSTITUENTID,
                    ADDRESS.ID as ADDRESSID,
                    CONSTITUENT.LOOKUPID as LOOKUPID,
                    CONSTITUENT.NAME as CONSTITUENTNAME,
                    case when CONSTITUENT.ISORGANIZATION = 1 then CONSTITUENT.NAME end as ORGNAME,
                    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.COUNTRYVALIDATIONINFO on COUNTRYVALIDATIONINFO.ID = ADDRESS.COUNTRYID and COUNTRYVALIDATIONINFO.ALLOWVALIDATION = 1
            where (CONSTITUENT.DONOTMAIL = @INCLUDECONSTITUENTDNM or CONSTITUENT.DONOTMAIL = 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));
        end
        else
        begin
            select top(@MAXROWS) CONSTITUENTID,
                    ADDRESS.ID as ADDRESSID,
                    CONSTITUENT.LOOKUPID as LOOKUPID,
                    CONSTITUENT.NAME as CONSTITUENTNAME,
                    case when CONSTITUENT.ISORGANIZATION = 1 then CONSTITUENT.NAME end as ORGNAME,
                    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.COUNTRYVALIDATIONINFO on COUNTRYVALIDATIONINFO.ID = ADDRESS.COUNTRYID and COUNTRYVALIDATIONINFO.ALLOWVALIDATION = 1
            where (CONSTITUENT.DONOTMAIL = @INCLUDECONSTITUENTDNM or CONSTITUENT.DONOTMAIL = 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));
        end
    end