USP_BBNC_CONSTITPHONEOREMAIL

            CREATE procedure dbo.USP_BBNC_CONSTITPHONEOREMAIL
            (
                @ID int,
                @PHONETYPECODEMAPID int
            )
            as
                set nocount on;

                select
                    EMAILADDRESSTYPECODE.DESCRIPTION as [PHONETYPE],
                    EMAILADDRESS.EMAILADDRESS as [NUMBER],
                    cast(1 as bit) as [ISEMAIL],
                    BBNCCODETABLEIDMAP.ID as SYSTEMID,
                    EMAILADDRESS.ISPRIMARY
                from
                    dbo.CONSTITUENT
                    inner join dbo.EMAILADDRESS on CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID
                    inner join dbo.EMAILADDRESSTYPECODE on EMAILADDRESS.EMAILADDRESSTYPECODEID = EMAILADDRESSTYPECODE.ID
                    inner join dbo.BBNCCODETABLEIDMAP on
                        EMAILADDRESS.EMAILADDRESSTYPECODEID = BBNCCODETABLEIDMAP.TABLEENTRYID
                        and 'E0473233-93ED-4509-9C42-65E037405497' = BBNCCODETABLEIDMAP.CODETABLECATALOGID
                where
                    CONSTITUENT.SEQUENCEID = @ID
                    and BBNCCODETABLEIDMAP.ID = @PHONETYPECODEMAPID

                union all

                select
                    PHONETYPECODE.DESCRIPTION as [PHONETYPE],
                    PHONEDETAILS.NUMBER,
                    cast(0 as bit) as [ISEMAIL],
                    BBNCCODETABLEIDMAP.ID as SYSTEMID,
                    PHONEDETAILS.ISPRIMARY
                from
                    dbo.CONSTITUENT
                    inner join (SELECT * FROM dbo.PHONE WHERE  ((PHONE.ENDDATE IS NULL) OR (PHONE.ENDDATE >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)))) PHONEDETAILS on CONSTITUENT.ID = PHONEDETAILS.CONSTITUENTID
                    inner join dbo.PHONETYPECODE on PHONEDETAILS.PHONETYPECODEID = PHONETYPECODE.ID
                    inner join dbo.BBNCCODETABLEIDMAP on
                        PHONEDETAILS.PHONETYPECODEID = BBNCCODETABLEIDMAP.TABLEENTRYID
                        and 'D57063E8-9DEA-4651-90A7-F86FE010AFC3' = BBNCCODETABLEIDMAP.CODETABLECATALOGID
                where
                    CONSTITUENT.SEQUENCEID = @ID
                    and BBNCCODETABLEIDMAP.ID = @PHONETYPECODEMAPID order by ISPRIMARY desc;