USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSSN

The load procedure used by the view dataform template "Constituent Social Security View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SSN	nvarchar(20)	INOUT	Social Security Number
@UNMASKEDSSN	nvarchar(20)	INOUT
@ISUSERALLOWEDTOVIEWFULLSSN	bit	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTSSN(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,        
    @SSN nvarchar(20) = null output,
    @UNMASKEDSSN nvarchar(20) = null output,
    @ISUSERALLOWEDTOVIEWFULLSSN bit = null output,
    @CURRENTAPPUSERID uniqueidentifier = null 
)
as
    set nocount on;

    -- Open the symmetric key for decryption

    exec dbo.USP_GET_KEY_ACCESS;

    set @DATALOADED = 0;

    -- Is user allowed to view sensitive information

        set @ISUSERALLOWEDTOVIEWFULLSSN = 0;
        declare @ISSYSADMIN bit = dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID);

        if @ISSYSADMIN = 0
        begin
                 set @ISUSERALLOWEDTOVIEWFULLSSN = dbo.[UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE](@CURRENTAPPUSERID, '84A0EC79-D4E1-45C8-BF00-714281B7A478'); 
        end
        else if @ISSYSADMIN = 1
        begin
                set @ISUSERALLOWEDTOVIEWFULLSSN = 1;
        end

    select 
            @DATALOADED = 1,
            @SSN = coalesce(convert(nvarchar(20), DecryptByKey(SSN)),'')
    from 
            dbo.CONSTITUENT
    where 
            CONSTITUENT.ID = @ID and
            ISORGANIZATION = 0 and
            ISGROUP = 0;

    close symmetric key sym_BBInfinity;


    if len(@SSN) = 0
    begin
        set @SSN = '';
        set @UNMASKEDSSN = '';
    end
    else
    begin
            set @SSN = replace(@SSN, '-', '');

            set @UNMASKEDSSN = @SSN;

            -- Use SSN formatting if SSN length is 9.

            if len(@SSN) = 9 
            begin
                    set @UNMASKEDSSN = substring(@UNMASKEDSSN, 1, 3) + '-' + substring(@UNMASKEDSSN, 4, 2) + '-' +  substring(@UNMASKEDSSN, 6, 4);

                    set @SSN = 'xxx-xx-' + substring(@SSN, 6, 4);
            end
            else
                    set @SSN = replicate('x', len(@SSN)- len(right(@SSN,4))) + right(@SSN,4);
    end

    return 0;