USP_CONSTITUENTDATAREVIEW_ADDRESS_LOADAUDITDATA

CREATE procedure dbo.USP_CONSTITUENTDATAREVIEW_ADDRESS_LOADAUDITDATA (
    @AUDITID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ADDRESSID uniqueidentifier = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @ADDRESSTYPECODEID uniqueidentifier = null output,
    @PRIMARY bit = null output,
    @DONOTMAIL bit = null output,
    @STARTDATE dbo.UDT_MONTHDAY = null output,
    @ENDDATE dbo.UDT_MONTHDAY = null output,
    @COUNTRYID uniqueidentifier = null output,
    @STATEID uniqueidentifier = null output,
    @ADDRESSBLOCK nvarchar(150) = null output,
    @CITY nvarchar(50) = null output,
    @POSTCODE nvarchar(12) = null output,
    @CART nvarchar(10) = null output,
    @DPC nvarchar(8) = null output,
    @LOT nvarchar(5) = null output,
    @SPOUSENAME nvarchar(154) = null output,
    @SPOUSEHASMATCHINGADDRESSES bit = null output,
    @UPDATEMATCHINGSPOUSEADDRESSES bit = null output,
    @OMITFROMVALIDATION bit = null output,                    
    @COUNTYCODEID uniqueidentifier = null output,
    @CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
    @STATEHOUSEDISTRICTCODEID uniqueidentifier = null output,
    @STATESENATEDISTRICTCODEID uniqueidentifier = null output,
    @LOCALPRECINCTCODEID uniqueidentifier = null output,
    @INFOSOURCECODEID uniqueidentifier = null output,
    @REGIONCODEID uniqueidentifier = null output,                    
    @LASTVALIDATIONATTEMPTDATE datetime = null output,
    @VALIDATIONMESSAGE nvarchar(200) = null output,
    @CERTIFICATIONDATA integer = null output,
    @NCOALASTSUBMITDATE datetime = null output,
    @NCOARETURN nvarchar(150) = null output,
    @NCOAFOOTNOTE nvarchar(150) = null output,
    @NCOADPVFOOTNOTE nvarchar(150) = null output,
    @NCOAMOVEDATE UDT_FUZZYDATE = null output,
    @NCOADMASUPPRESSION bit = null output,
    @NCOAMAILGRADE nvarchar(150) = null output,
    @VALIDATIONCOUNTRIES xml = null output,
    @ZIPLOOKUPCOUNTRIES xml = null output,
    @UPDATECONTACTS bit = null output,
    @HASCONTACTS bit = null output,
    @DONOTMAILREASONCODEID uniqueidentifier = null output,
    @HISTORICALSTARTDATE date = null output,
    @HISTORICALENDDATE date = null output,
    @DATEADDED date = null output,
    @INFOSOURCECOMMENTS nvarchar(256) = null output,
    @ISCONFIDENTIAL bit = null output,
    @ORIGINCODE tinyint = null output
)
as
begin
    set nocount on;

    declare @DATECHANGED datetime;
    declare @SPOUSEID uniqueidentifier;

    set @DATALOADED = 0;
    set @SPOUSEHASMATCHINGADDRESSES = 0;
    set @UPDATEMATCHINGSPOUSEADDRESSES = 0;

    if exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP')
        begin
            select
                @DATALOADED = 1,
                @ADDRESSID = ADDRESSAUDIT.AUDITRECORDID,
                @DATECHANGED = ADDRESSAUDIT.DATECHANGED,
                @CONSTITUENTID = ADDRESSAUDIT.CONSTITUENTID,
                @ADDRESSTYPECODEID = ADDRESSAUDIT.ADDRESSTYPECODEID,
                @PRIMARY = ADDRESSAUDIT.ISPRIMARY,
                @DONOTMAIL = ADDRESSAUDIT.DONOTMAIL,
                @STARTDATE = ADDRESSAUDIT.STARTDATE,
                @ENDDATE = ADDRESSAUDIT.ENDDATE,
                @COUNTRYID = ADDRESSAUDIT.COUNTRYID,
                @STATEID = ADDRESSAUDIT.STATEID,
                @ADDRESSBLOCK = ADDRESSAUDIT.ADDRESSBLOCK,
                @CITY = ADDRESSAUDIT.CITY,
                @POSTCODE = ADDRESSAUDIT.POSTCODE,
                @CART = ADDRESSAUDIT.CART,
                @DPC = ADDRESSAUDIT.DPC,
                @LOT = ADDRESSAUDIT.LOT,
                @SPOUSEID = SPOUSE.ID,
                @SPOUSENAME = SPOUSE.NAME,                                                                
                @DONOTMAILREASONCODEID = ADDRESSAUDIT.DONOTMAILREASONCODEID,
                @HISTORICALSTARTDATE = ADDRESSAUDIT.HISTORICALSTARTDATE,
                @HISTORICALENDDATE = ADDRESSAUDIT.HISTORICALENDDATE,
                @DATEADDED = ADDRESSAUDIT.DATEADDED,
                @ISCONFIDENTIAL = ADDRESSAUDIT.ISCONFIDENTIAL
            from
                dbo.ADDRESSAUDIT
            left join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = ADDRESSAUDIT.CONSTITUENTID and RELATIONSHIP.ISSPOUSE = 1
            left join dbo.CONSTITUENT as SPOUSE on SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
            where
                ADDRESSAUDIT.AUDITID = @AUDITID;

            if exists (
                select ADDRESS.ID
                from dbo.ADDRESS
                where
                    ADDRESS.CONSTITUENTID = @SPOUSEID and
                    ADDRESS.COUNTRYID = @COUNTRYID and
                    ADDRESS.ADDRESSBLOCK = @ADDRESSBLOCK and
                    ADDRESS.CITY = @CITY and
                    (ADDRESS.STATEID = @STATEID or (ADDRESS.STATEID is null and @STATEID is null)) and
                    ADDRESS.POSTCODE = @POSTCODE
                ) begin
                    set @SPOUSEHASMATCHINGADDRESSES = 1;    
                    set @UPDATEMATCHINGSPOUSEADDRESSES = 1;
            end

            if exists(
                select r.ID
                from dbo.RELATIONSHIP r
                    inner join dbo.ADDRESSAUDIT a1 on a1.CONSTITUENTID=r.RELATIONSHIPCONSTITUENTID
                    inner join dbo.ADDRESS a2 on a2.RELATIONSHIPID=r.ID
                where 
                    a1.AUDITID=@AUDITID and
                    r.ISCONTACT=1 and
                    a1.COUNTRYID=a2.COUNTRYID and
                    (a1.STATEID=a2.STATEID or (a1.STATEID is null and a2.STATEID is null)) and
                    (a1.ADDRESSBLOCK=a2.ADDRESSBLOCK or (a1.ADDRESSBLOCK is null and a2.ADDRESSBLOCK is null)) and
                    (a1.CITY=a2.CITY or (a1.CITY is null and a2.CITY is null)) and
                    (a1.POSTCODE=a2.POSTCODE or (a1.POSTCODE is null and a2.POSTCODE is null)))
                begin
                    set @HASCONTACTS=1
                end
                else
                begin
                    set @HASCONTACTS=0
                end
        end
    else
        select
            @DATALOADED = 1,
            @ADDRESSID = ADDRESSAUDIT.AUDITRECORDID,
            @DATECHANGED = ADDRESSAUDIT.DATECHANGED,
            @HASCONTACTS = 0,
            @ADDRESSTYPECODEID = ADDRESSAUDIT.ADDRESSTYPECODEID,
            @PRIMARY = ADDRESSAUDIT.ISPRIMARY,
            @DONOTMAIL = ADDRESSAUDIT.DONOTMAIL,
            @STARTDATE = ADDRESSAUDIT.STARTDATE,
            @ENDDATE = ADDRESSAUDIT.ENDDATE,
            @COUNTRYID = ADDRESSAUDIT.COUNTRYID,
            @STATEID = ADDRESSAUDIT.STATEID,
            @ADDRESSBLOCK = ADDRESSAUDIT.ADDRESSBLOCK,
            @CITY = ADDRESSAUDIT.CITY,
            @POSTCODE = ADDRESSAUDIT.POSTCODE,
            @CART = ADDRESSAUDIT.CART,
            @DPC = ADDRESSAUDIT.DPC,
            @LOT = ADDRESSAUDIT.LOT,
            @DONOTMAILREASONCODEID = ADDRESSAUDIT.DONOTMAILREASONCODEID,
            @HISTORICALSTARTDATE = ADDRESSAUDIT.HISTORICALSTARTDATE,
            @HISTORICALENDDATE = ADDRESSAUDIT.HISTORICALENDDATE,
            @DATEADDED = ADDRESSAUDIT.DATEADDED,
            @ISCONFIDENTIAL = ADDRESSAUDIT.ISCONFIDENTIAL
        from
            dbo.ADDRESSAUDIT
        where
            ADDRESSAUDIT.AUDITID = @AUDITID;

    -- get addressvalidationupdate row version appropriate to the selected audit row    

    select top 1
        @OMITFROMVALIDATION = OMITFROMVALIDATION,                            
        @COUNTYCODEID = COUNTYCODEID,
        @CONGRESSIONALDISTRICTCODEID = CONGRESSIONALDISTRICTCODEID,
        @STATEHOUSEDISTRICTCODEID = STATEHOUSEDISTRICTCODEID,
        @STATESENATEDISTRICTCODEID = STATESENATEDISTRICTCODEID,
        @LOCALPRECINCTCODEID = LOCALPRECINCTCODEID,
        @INFOSOURCECODEID = INFOSOURCECODEID,
        @ORIGINCODE = ORIGINCODE,
        @INFOSOURCECOMMENTS = INFOSOURCECOMMENTS,
        @REGIONCODEID = REGIONCODEID,                                
        @LASTVALIDATIONATTEMPTDATE = LASTVALIDATIONATTEMPTDATE,
        @VALIDATIONMESSAGE = VALIDATIONMESSAGE,
        @CERTIFICATIONDATA = CERTIFICATIONDATA,
        @NCOALASTSUBMITDATE = NCOALASTSUBMITDATE,
        @NCOARETURN = case [NCOARETURNCODE]
                        when (0) then N''
                        when (1) then N'A - COA Match'
                        when (2) then N'00 - No match'
                        when (3) then N'01 - Match to undeliverable: Foreign move'
                        when (4) then N'02 - Match to undeliverable: Moved left no address (MLNA)'
                        when (5) then N'03 - Match to undeliverable: Box closed no order (BCNO)'
                        when (6) then N'05 - Found COA: New 11-digit DPBC is ambiguous'
                        when (7) then N'14 - Found COA: New address would not convert at run time'
                        when (8) then N'19 - Found COA: New address not ZIP + 4 coded'
                        when (9) then N'91 - COA match: Secondary number ignored on COA'
                        when (10) then N'92 - COA match: Secondary number ignored on input address'
                        when (11) then N'04 - Cannot match COA: Street address with secondary'
                        when (12) then N'06 - Cannot match COA: Multiple matches: Middle name related'
                        when (13) then N'07 - Cannot match COA: Multiple matches: Gender related'
                        when (14) then N'08 - Cannot match COA: Multiple matches: Address related'
                        when (15) then N'09 - Cannot match COA: Highrise default'
                        when (16) then N'10 - Cannot match COA: Rural default'
                        when (17) then N'11 - Cannot match COA: Individual match: Insufficient COA name for match'
                        when (18) then N'12 - Cannot match COA: Middle name test failed'
                        when (19) then N'13 - Cannot match COA: Gender test failed'
                        when (20) then N'15 - Cannot match COA: Individual name insufficient'
                        when (21) then N'16 - Cannot match COA: Secondary number discrepancy'
                        when (22) then N'17 - Cannot match COA: Other insufficient name'
                        when (23) then N'18 - Cannot match COA: General delivery'
                        when (24) then N'20 - Cannot match COA: Conflicting directions after re-chaining'
                        when (25) then N'66 - Daily delete'
                      end,
        @NCOAFOOTNOTE = case [NCOAFOOTNOTECODE]
                          when (0) then N''
                          when (1) then N'M - Forwardable move'
                          when (2) then N'K - Moved, no forwarding address'
                          when (3) then N'G - PO box closed'
                          when (4) then N'F - Move to foreign address'
                          when (5) then N'S - Forwardable move, but secondary address cannot be verified'
                          when (6) then N'P - Change of address form filed, but new address not in USPS records'
                          when (7) then N'L - Temporary address has lapsed'
                        end,
        @NCOADPVFOOTNOTE = case [NCOADPVFOOTNOTECODE]
                             when (0) then N''
                             when (1) then N'AA - Input address matched to the ZIP + 4 file'
                             when (2) then N'A1 - Input address not matched to the ZIP + 4 file'
                             when (3) then N'BB - Input address matched to DPV (all components)'
                             when (4) then N'CC - Input address primary number matched to DPV but secondary number present but not valid'
                             when (5) then N'F1 - Military'
                             when (6) then N'G1 - General Delivery'
                             when (7) then N'N1 - Input address primary number matched to DPV but high-rise address missing secondary number'
                             when (8) then N'M1 - Input address primary number missing'
                             when (9) then N'M3 - Input address primary number invalid'
                             when (10) then N'NL - NCOALink move address cannot be DPV confirmed (occurs because the NCOAlink directories are updated more frequently than the DPV directories)'
                             when (11) then N'P1 - Input address missing PO, RR or HC box number'
                             when (12) then N'P3 - Input address is invalid PO, RR, or HC number'
                             when (13) then N'RR - Input address matched to CMRA'
                             when (14) then N'R1 - Input address matched to CMRA but secondary number not present'
                             when (15) then N'U1 - Unique'
                           end,
        @NCOAMOVEDATE = NCOAMOVEDATE,
        @NCOADMASUPPRESSION = NCOADMASUPPRESSION,
        @NCOAMAILGRADE = case [NCOAMAILGRADECODE]
                           when (0) then N''
                           when (1) then N'A - This is an accurate, mail-able address'
                           when (2) then N'B - Record is probably deliverable'
                           when (3) then N'C - The record may or may not be deliverable'
                           when (4) then N'F - The record is undeliverable. Do not mail'
                         end
    from dbo.ADDRESSVALIDATIONUPDATEAUDIT
    where AUDITRECORDID = @ADDRESSID
    and DATECHANGED <= @DATECHANGED
    order by AUDITDATE desc;

    -- if no valid audit rows found, use current version as long as it existed at the time of the selected audit

    if @@ROWCOUNT = 0
        select
            @OMITFROMVALIDATION = OMITFROMVALIDATION,                            
            @COUNTYCODEID = COUNTYCODEID,
            @CONGRESSIONALDISTRICTCODEID = CONGRESSIONALDISTRICTCODEID,
            @STATEHOUSEDISTRICTCODEID = STATEHOUSEDISTRICTCODEID,
            @STATESENATEDISTRICTCODEID = STATESENATEDISTRICTCODEID,
            @LOCALPRECINCTCODEID = LOCALPRECINCTCODEID,
            @INFOSOURCECODEID = INFOSOURCECODEID,
            @ORIGINCODE = ORIGINCODE,
            @INFOSOURCECOMMENTS = INFOSOURCECOMMENTS,
            @REGIONCODEID = REGIONCODEID,                                
            @LASTVALIDATIONATTEMPTDATE = LASTVALIDATIONATTEMPTDATE,
            @VALIDATIONMESSAGE = VALIDATIONMESSAGE,
            @CERTIFICATIONDATA = CERTIFICATIONDATA,
            @NCOALASTSUBMITDATE = NCOALASTSUBMITDATE,
            @NCOARETURN = NCOARETURN,
            @NCOAFOOTNOTE = NCOAFOOTNOTE,
            @NCOADPVFOOTNOTE = NCOADPVFOOTNOTE,
            @NCOAMOVEDATE = NCOAMOVEDATE,
            @NCOADMASUPPRESSION = NCOADMASUPPRESSION,
            @NCOAMAILGRADE = NCOAMAILGRADE
        from dbo.ADDRESSVALIDATIONUPDATE
        where ID = @ADDRESSID
        and DATEADDED <= @DATECHANGED;

    if @ORIGINCODE is null
        set @ORIGINCODE = 0

    select @VALIDATIONCOUNTRIES = dbo.UFN_COUNTRY_GETVALIDATIONCOUNTRIES_TOITEMLISTXML();
    select @ZIPLOOKUPCOUNTRIES = dbo.UFN_ZIPCITYSTATE_GETCOUNTRIES_TOITEMLISTXML();

    return 0;
end