USP_BBNC_CONSTITBIODATA

            CREATE procedure dbo.USP_BBNC_CONSTITBIODATA(@ID int)
            as

                set NOCOUNT on;

                declare @NOEMAILSOLICITCODEID uniqueidentifier
                select top 1 @NOEMAILSOLICITCODEID=DONOTEMAILSOLICITCODEID
                from NETCOMMUNITYDEFAULTCODEMAP

                declare @CONSTITUENTSOLICITCODEID uniqueidentifier
                select @CONSTITUENTSOLICITCODEID=csc.ID 
                from CONSTITUENTSOLICITCODE csc
                    join CONSTITUENT c on c.ID=csc.CONSTITUENTID
                where c.SEQUENCEID=@ID and 
                    csc.SOLICITCODEID=@NOEMAILSOLICITCODEID and
                    ((csc.STARTDATE is null) or (datediff(day, csc.STARTDATE, getdate())>=0)) and
                    ((csc.ENDDATE is null) or (datediff(day, getdate(), csc.ENDDATE)>=0))

                declare @REQUESTNOEMAIL bit
                if @CONSTITUENTSOLICITCODEID is null
                    set @REQUESTNOEMAIL = 0
                else
                    set @REQUESTNOEMAIL = 1

                select
                    CONSTITUENT.BIRTHDATE,
                    CONSTITUENT.FIRSTNAME,
                    CONSTITUENT.NAME,
          CONSTITGENDER.DESCRIPTION as GENDER,
                    CONSTITUENT.KEYNAME,
                    CONSTITUENT.MAIDENNAME,
                    CONSTITUENT.MIDDLENAME,
                    CONSTITUENT.NICKNAME,
                    TITLECODE.DESCRIPTION as TITLECODE,
                    TITLE2CODE.DESCRIPTION as TITLE2CODE,
                    SUFFIXCODE.DESCRIPTION as SUFFIXCODE,
                    SUFFIX2CODE.DESCRIPTION as SUFFIX2CODE,
                    @REQUESTNOEMAIL [REQUESTNOEMAIL],
                    CONSTITUENT.LOOKUPID,
                    MARITALSTATUSCODE.DESCRIPTION as MARITALSTATUSCODE,
                    CONSTITUENT.WEBADDRESS,
                    CONSTITUENT.ISORGANIZATION,
                    INDUSTRYCODE.DESCRIPTION as INDUSTRYCODE,
                    ORGANIZATIONDATA.NUMEMPLOYEES,
                    ORGANIZATIONDATA.NUMSUBSIDIARIES,
                    cast(case when DECEASEDCONSTITUENT.ID is null then 0 else 1 end as bit) [DECEASED],
                    case when DECEASEDCONSTITUENT.DECEASEDDATE is null then '00000000' else DECEASEDCONSTITUENT.DECEASEDDATE end [DECEASEDDATE],
          TARGETCODE.DESCRIPTION as TARGET,
          INCOMECODE.DESCRIPTION as INCOME,          
          CONSTITUENT.GIVESANONYMOUSLY
                from
                    dbo.CONSTITUENT
        left join
          dbo.DEMOGRAPHIC on CONSTITUENT.ID = DEMOGRAPHIC.ID
        left join
          dbo.TARGETCODE on DEMOGRAPHIC.TARGETCODEID = TARGETCODE.ID
        left join
          dbo.INCOMECODE on DEMOGRAPHIC.INCOMECODEID = INCOMECODE.ID
                left join
                    dbo.TITLECODE on CONSTITUENT.TITLECODEID = TITLECODE.ID
                left join
                    dbo.TITLECODE TITLE2CODE on CONSTITUENT.TITLE2CODEID = TITLE2CODE.ID
        left join 
          dbo.GENDERCODE as CONSTITGENDER on CONSTITUENT.GENDERCODEID = CONSTITGENDER.ID
                left join
                    dbo.SUFFIXCODE on CONSTITUENT.SUFFIXCODEID = SUFFIXCODE.ID
                left join
                    dbo.SUFFIXCODE SUFFIX2CODE on CONSTITUENT.SUFFIX2CODEID = SUFFIX2CODE.ID
                left join
                    dbo.MARITALSTATUSCODE on CONSTITUENT.MARITALSTATUSCODEID = MARITALSTATUSCODE.ID
                left join
                    dbo.RELATIONSHIP
                on
                    RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and
                    RELATIONSHIP.ISSPOUSE = 1
                left join
                    dbo.ORGANIZATIONDATA on CONSTITUENT.ID = ORGANIZATIONDATA.ID
                left join
                    dbo.INDUSTRYCODE on ORGANIZATIONDATA.INDUSTRYCODEID = INDUSTRYCODE.ID
                left join
                    dbo.DECEASEDCONSTITUENT on CONSTITUENT.ID = DECEASEDCONSTITUENT.ID
                where
                    CONSTITUENT.SEQUENCEID = @ID;