USP_BBNC_CONSTITRELATEDORGSDATA

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

            select
                BBNCRELATIONSHIPIDMAP.ID as [RELATIONSHIPMAPID], --JamesWill 05/19/2008 CR300940-051908 Return the reciprocal relationship typecode ID so BBNC can find this relationship

                [RECIPROCALCONSTITUENT].SEQUENCEID as [MAPID],
                [RECIPROCALCONSTITUENT].NAME,
                COUNTRY.DESCRIPTION as [COUNTRYLONG],
                COUNTRY.ABBREVIATION as [COUNTRYSHORT],
                ADDRESS.ADDRESSBLOCK,
                ADDRESS.CITY,
                STATE.ABBREVIATION as [STATE],
                ADDRESS.POSTCODE,
                dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID) as [FORMATTEDADDRESS],
                COUNTRY.COUNTRYADDRESSFORMATID,
                RELATIONSHIP.ISCONTACT,
                RELATIONSHIPTYPECODE.DESCRIPTION as [RELATIONSHIPTYPECODE],
        RELATIONSHIPTYPECODE.ID as [RELATIONSHIPTYPECODEID],
        RELATIONSHIP.ISMATCHINGGIFTRELATIONSHIP,
                RELATIONSHIP.COMMENTS,
                RELATIONSHIP.STARTDATE,
                RELATIONSHIP.ENDDATE,
                RELATIONSHIP.CONTACTTYPECODEID,
        RELATIONSHIP.ID as [RELATIONSHIPID],
        RELATIONSHIP.RELATIONSHIPTYPECODEID as [RECIPROCALTYPECODEID]
            from dbo.CONSTITUENT
            inner join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID
            inner join dbo.BBNCRELATIONSHIPIDMAP on BBNCRELATIONSHIPIDMAP.RELATIONSHIPID = RELATIONSHIP.ID
            inner join dbo.RELATIONSHIPTYPECODE on RELATIONSHIPTYPECODE.ID = RELATIONSHIP.RECIPROCALTYPECODEID
            --inner join dbo.BBNCCODETABLEIDMAP on BBNCCODETABLEIDMAP.TABLEENTRYID = RELATIONSHIP.RELATIONSHIPTYPECODEID and BBNCCODETABLEIDMAP.CODETABLECATALOGID = 'F4ADA947-98CE-4D74-A667-101B8961AD93' --RELATIONSHIPTYPECODE

            --inner join dbo.BBNCCODETABLEIDMAP on BBNCCODETABLEIDMAP.TABLEENTRYID = RELATIONSHIP.RECIPROCALTYPECODEID and BBNCCODETABLEIDMAP.CODETABLECATALOGID = 'F4ADA947-98CE-4D74-A667-101B8961AD93' --RELATIONSHIPTYPECODE

            inner join dbo.CONSTITUENT as [RECIPROCALCONSTITUENT] on [RECIPROCALCONSTITUENT].ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
            left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = [RECIPROCALCONSTITUENT].ID and ADDRESS.ISPRIMARY = 1
            left join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID
            left join dbo.STATE on STATE.ID = ADDRESS.STATEID
            where CONSTITUENT.SEQUENCEID = @ID
                and [RECIPROCALCONSTITUENT].ISORGANIZATION = 1
                and [RECIPROCALCONSTITUENT].ISGROUP = 0 --Do not return groups in this query