USP_BBNC_MGCONSTITDATA

            CREATE procedure dbo.USP_BBNC_MGCONSTITDATA(@ID int) 
            as

            set nocount on;

            declare @BUSINESSPHONETYPECODEID uniqueidentifier;
            declare @FAXPHONETYPECODEID uniqueidentifier;
            declare @MGCONDITIONTYPECODEID uniqueidentifier;
            declare @ALIASTYPECODEID uniqueidentifier;

            select 
                @BUSINESSPHONETYPECODEID = coalesce(BUSINESSPHONECODEID, '00000000-0000-0000-0000-000000000000'),
                @FAXPHONETYPECODEID = coalesce(FAXPHONECODEID, '00000000-0000-0000-0000-000000000000'),
                @MGCONDITIONTYPECODEID = coalesce(MGCONDITIONTYPECODEID, '00000000-0000-0000-0000-000000000000'),
                @ALIASTYPECODEID = coalesce(MGALIASTYPECODEID, '00000000-0000-0000-0000-000000000000')
            from dbo.NETCOMMUNITYDEFAULTCODEMAP

            select 
                CONSTITUENT.KEYNAME,
                (select DESCRIPTION from dbo.INDUSTRYCODE where ID = [ORG].INDUSTRYCODEID),
                CONSTITUENT.WEBADDRESS,
                ADDRESS.ADDRESSBLOCK,
                ADDRESS.CITY,
                STATE.ABBREVIATION as [STATE],
                ADDRESS.POSTCODE,
                COUNTRY.DESCRIPTION as COUNTRYLONG,
                COUNTRY.ABBREVIATION as COUNTRYSHORT,
                [BUSINESSPHONE].NUMBER as [BUSINESSPHONE],
                [FAXPHONE].NUMBER as [FAXPHONE],
                coalesce([MGC].MAXMATCHPERGIFT, 0) as [MAXMATCHPERGIFT],
                coalesce([MGC].MATCHINGFACTOR, 0) as [MATCHINGFACTOR],
                coalesce([MGC].NOTES, '') as [MATCHNOTES],
                INDUSTRYCODE.DESCRIPTION as [INDUSTRYCODE],
                coalesce([MGC].MINMATCHPERGIFT, 0) as [MINMATCHPERGIFT],
                coalesce([MGC].MAXMATCHANNUAL, 0) as [MAXMATCHANNUAL],
                coalesce([MGC].MAXMATCHTOTAL, 0) as [MAXMATCHTOTAL],
                coalesce(ALIAS.KEYNAME, '') as [ALIAS]
            from dbo.CONSTITUENT
            left join dbo.ORGANIZATIONDATA as [ORG] on [ORG].ID = CONSTITUENT.ID
            left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
            left join dbo.STATE on STATE.ID = ADDRESS.STATEID
            left join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID
            left join dbo.PHONE as [BUSINESSPHONE] on [BUSINESSPHONE].CONSTITUENTID = CONSTITUENT.ID and [BUSINESSPHONE].PHONETYPECODEID = @BUSINESSPHONETYPECODEID
            left join dbo.PHONE as [FAXPHONE] on [FAXPHONE].CONSTITUENTID = CONSTITUENT.ID and [FAXPHONE].PHONETYPECODEID = @FAXPHONETYPECODEID
            left join dbo.MATCHINGGIFTCONDITION as [MGC] on [MGC].ORGANIZATIONID = CONSTITUENT.ID and [MGC].MATCHINGGIFTCONDITIONTYPECODEID = @MGCONDITIONTYPECODEID
            left join dbo.INDUSTRYCODE on INDUSTRYCODE.ID = [ORG].INDUSTRYCODEID
            left join dbo.ALIAS on ALIAS.CONSTITUENTID = CONSTITUENT.ID and ALIAS.ALIASTYPECODEID = @ALIASTYPECODEID
            where CONSTITUENT.SEQUENCEID = @ID;