USP_BBNC_CONSTITORGDATA

            CREATE procedure dbo.USP_BBNC_CONSTITORGDATA
            (
                @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.KEYNAME as [ORGNAME],
                0 [INCOME], 
                0 [TARGET], -- these fields were never supported for CRM

                INDUSTRYCODE.DESCRIPTION as [INDUSTRYCODE],
                ORGANIZATIONDATA.NUMEMPLOYEES,
                ORGANIZATIONDATA.NUMSUBSIDIARIES,
                CONSTITUENT.LOOKUPID,
                CONSTITUENT.WEBADDRESS,
                @REQUESTNOEMAIL as [REQUESTNOEMAIL]
            from dbo.CONSTITUENT
            left join dbo.DEMOGRAPHIC on CONSTITUENT.ID = DEMOGRAPHIC.ID 
            left join dbo.ORGANIZATIONDATA on CONSTITUENT.ID = ORGANIZATIONDATA.ID 
            left join dbo.INDUSTRYCODE on ORGANIZATIONDATA.INDUSTRYCODEID = INDUSTRYCODE.ID 
            where CONSTITUENT.SEQUENCEID = @ID