UFN_CONSTITUENT_ADDRESSES

CREATE function dbo.UFN_CONSTITUENT_ADDRESSES
(
    @CONSTITUENTID uniqueidentifier, 
    @INCLUDEFORMER bit    = 0
)
returns @OUTPUT table
(
    ID uniqueidentifier, 
    DESCRIPTION nvarchar(300),
    ADDRESSTYPE nvarchar(100),
    ISPRIMARY bit,
    DONOTMAIL bit,
    ISCONFIDENTIAL bit,
    ISFORMER bit,
    HISTORICALSTARTDATE datetime,
    HISTORICALENDDATE datetime,
    ISGEOCODED bit,
    PENDINGGEOCODE bit,
    INVALIDGEOCODE bit,
    MAPCONTEXTID nvarchar(108),
    IMAGEKEY nvarchar(15),
    SEASONALSTARTDATE dbo.UDT_MONTHDAY,
    SEASONALENDDATE dbo.UDT_MONTHDAY,
    STARTDATE_SORT date,
    ENDDATE_SORT date,
    DATEADDED datetime
)
as begin

    declare @CURRENTDATE datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
    declare @MAPENTITYID uniqueidentifier = N'645E6BE7-459C-402b-A03C-67587CA72B94';

    if (dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('6f77d512-d0d1-444f-9b46-b8603a6fe5f1') = 1)
        set @MAPENTITYID = N'78102CE5-F217-4429-9175-D99A6B55A4EE';

    with [CONTACTINFO_CTE] as
    (
        --Addresses

        select 
            ADDRESS.ID,
            ADDRESS.DESCRIPTION as CONTACTINFO,
            dbo.UFN_ADDRESSTYPECODE_GETDESCRIPTION(ADDRESS.ADDRESSTYPECODEID) as TYPE,
            ADDRESS.ISPRIMARY,
            ADDRESS.DONOTMAIL,
            ADDRESS.ISCONFIDENTIAL,
            ADDRESS.HISTORICALSTARTDATE,
            ADDRESS.HISTORICALENDDATE,
            case when ADDRESS.HISTORICALSTARTDATE is null then '0001-01-01' else ADDRESS.HISTORICALSTARTDATE end as [STARTDATE_SORT],
            case when ADDRESS.HISTORICALENDDATE is null then '0001-01-01' else ADDRESS.HISTORICALENDDATE end as [ENDDATE_SORT],
            ADDRESS.DATEADDED,
            ADDRESSCOORDINATES.ID as ADDRESSCOORDINATESID,
            ADDRESSCOORDINATES.PENDINGGEOCODE,
            ADDRESSCOORDINATES.INVALIDGEOCODE,
            dbo.UFN_MAPPING_BUILDCONTEXTRECORDID(@MAPENTITYID, @CONSTITUENTID, ADDRESS.ID) as [MAPCONTEXTID],
            ADDRESS.STARTDATE,
            ADDRESS.ENDDATE
        from
            dbo.ADDRESS
            left join dbo.ADDRESSCOORDINATES on ADDRESSCOORDINATES.ADDRESSID = ADDRESS.ID
        where
            ADDRESS.CONSTITUENTID = @CONSTITUENTID
    )
    insert into @OUTPUT
    (
        ID, 
        DESCRIPTION,
        ADDRESSTYPE,
        ISPRIMARY,
        DONOTMAIL,
        ISCONFIDENTIAL,
        ISFORMER,
        HISTORICALSTARTDATE,
        HISTORICALENDDATE,
        ISGEOCODED,
        PENDINGGEOCODE,
        INVALIDGEOCODE,
        MAPCONTEXTID,
        IMAGEKEY,
        SEASONALSTARTDATE,
        SEASONALENDDATE,
        STARTDATE_SORT,
        ENDDATE_SORT,
        DATEADDED
    )
    select
        CONTACTINFO.ID,
        CONTACTINFO.CONTACTINFO,
        CONTACTINFO.TYPE,
        CONTACTINFO.ISPRIMARY,
        CONTACTINFO.DONOTMAIL,
        CONTACTINFO.ISCONFIDENTIAL,
        case when ((CONTACTINFO.HISTORICALENDDATE is null) or (CONTACTINFO.HISTORICALENDDATE > @CURRENTDATE)) then 0 else 1 end as ISFORMER,
        CONTACTINFO.HISTORICALSTARTDATE,
        CONTACTINFO.HISTORICALENDDATE,
        case when CONTACTINFO.ADDRESSCOORDINATESID is null or CONTACTINFO.PENDINGGEOCODE = 1 or CONTACTINFO.INVALIDGEOCODE = 1 then 0 else 1 end as ISGEOCODED,
        case when CONTACTINFO.ADDRESSCOORDINATESID is null then 0 else CONTACTINFO.PENDINGGEOCODE end as PENDINGGEOCODE,
        case when CONTACTINFO.ADDRESSCOORDINATESID is null then 0 else CONTACTINFO.INVALIDGEOCODE end as INVALIDGEOCODE,
        CONTACTINFO.MAPCONTEXTID,        
        case
            when CONTACTINFO.ISCONFIDENTIAL = 1
                then 'RES:padlock'
            when CONTACTINFO.DONOTMAIL = 1
                then 'RES:warning'
            else
                'RES:lv_spacer'
        end as IMAGEKEY,
        CONTACTINFO.STARTDATE,
        CONTACTINFO.ENDDATE,
        CONTACTINFO.STARTDATE_SORT,
        CONTACTINFO.ENDDATE_SORT,
        CONTACTINFO.DATEADDED
    from
        [CONTACTINFO_CTE] CONTACTINFO
    where
        @INCLUDEFORMER = 1 or ((CONTACTINFO.HISTORICALENDDATE is null) or (CONTACTINFO.HISTORICALENDDATE > @CURRENTDATE))

    return;
end