USP_BBNC_CONSTITPRIMARYBUSINESSDATA

            CREATE procedure dbo.USP_BBNC_CONSTITPRIMARYBUSINESSDATA
            (
                @ID int
            )
            as
                set nocount on;

                --purposefully using DATE

                declare @CURRENTDATE date;
                set @CURRENTDATE = getdate();

                select top 1
                    case when RELATIONSHIP.ISPRIMARYBUSINESS = 1 then BUSINESS.NAME else null end as [NAME],
                    case when RELATIONSHIP.ISPRIMARYBUSINESS = 1 then RELATIONSHIPJOBINFO.JOBTITLE else null end as [POSITION],
                    case when RELATIONSHIP.ISPRIMARYBUSINESS = 1 then INDUSTRYCODE.DESCRIPTION else null end as [INDUSTRY],
                    COUNTRY.DESCRIPTION as COUNTRYLONG,
                    COUNTRY.ABBREVIATION as COUNTRYSHORT,
                    ADDRESS.ADDRESSBLOCK,
                    ADDRESS.CITY,
                    case
                        --TMV 04/11/2007 CR272266-040907 BBNC expects the full description for New Zealand city, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.

                        --TMV 04/13/2007 CR272671-041207 BBNC expects the full description for United Kingdom county, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.

                        when 
                        (
                            COUNTRY.COUNTRYADDRESSFORMATID = '959809FB-8FA7-4A19-888F-9951BD9B29D3' --United Kingdom

                            or
                            COUNTRY.COUNTRYADDRESSFORMATID = 'A3B050A5-E1C8-4E1B-99AE-40E9FCADA0BC' --New Zealand

                        )
                        then
                            STATE.DESCRIPTION
                        else
                            STATE.ABBREVIATION
                    end as STATE,
                    ADDRESS.POSTCODE,
                    dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID) as FORMATTEDADDRESS,
                    COUNTRY.COUNTRYADDRESSFORMATID,
                    REGIONCODE.DESCRIPTION as REGION
                from
                    dbo.CONSTITUENT
                left join
                    dbo.RELATIONSHIP on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                left join
                    dbo.RELATIONSHIPJOBINFO on RELATIONSHIP.RELATIONSHIPSETID = RELATIONSHIPJOBINFO.RELATIONSHIPSETID
                        and @CURRENTDATE between coalesce(RELATIONSHIPJOBINFO.STARTDATE, @CURRENTDATE) and coalesce(RELATIONSHIPJOBINFO.ENDDATE, @CURRENTDATE)
                left join
                    dbo.CONSTITUENT as BUSINESS on RELATIONSHIP.RECIPROCALCONSTITUENTID = BUSINESS.ID
                left join
                    dbo.ORGANIZATIONDATA on BUSINESS.ID = ORGANIZATIONDATA.ID
                left join
                    dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID -- Pull the address from the constituent, not their business.

                    and ADDRESS.ADDRESSTYPECODEID = (select top 1 BUSINESSADDRESSTYPECODEID from dbo.NETCOMMUNITYDEFAULTCODEMAP)
                left join
                    dbo.INDUSTRYCODE on ORGANIZATIONDATA.INDUSTRYCODEID = INDUSTRYCODE.ID
                left join
                    dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
                left join
                    dbo.STATE on ADDRESS.STATEID = STATE.ID
                left join
                    dbo.ADDRESSVALIDATIONUPDATE on ADDRESSVALIDATIONUPDATE.ID = ADDRESS.ID
                left join
                    dbo.REGIONCODE on REGIONCODE.ID = ADDRESSVALIDATIONUPDATE.REGIONCODEID
                where 
                    CONSTITUENT.SEQUENCEID = @ID
                    and CONSTITUENT.ISORGANIZATION = 0 --JamesWill CR272664-041207 2007/04/25 Organizations don't have primary businesses, so don't return data for them

                    and CONSTITUENT.ISGROUP = 0 --JohnLu 2007/08/20 Guarantee that only Individuals are returned

          and (ADDRESS.HISTORICALENDDATE is null or ADDRESS.HISTORICALENDDATE > CONVERT(date,GETDATE()))
                order by
                    RELATIONSHIP.ISPRIMARYBUSINESS desc, ADDRESS.ISPRIMARY desc, ADDRESS.SEQUENCE;