USP_BBNC_CONSTITPRIMARYALUMNIDATA_1_1

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

        declare @USEACADEMICCATALOG bit;
              select top 1
                  @USEACADEMICCATALOG = USEACADEMICCATALOG
              from
                  dbo.EDUCATIONALCONFIGURATION;

              set @USEACADEMICCATALOG = coalesce(@USEACADEMICCATALOG,0);

                select 
                    BBNCEDUCATIONALHISTORYIDMAP.ID as EDUCATIONALHISTORYMAPID,
                    EDUCATIONALINSTITUTION.NAME,
                    EDUCATIONALHISTORYTYPECODE.DESCRIPTION as SCHOOLTYPE,
                    --EDUCATIONALHISTORYSTATUSCODE.DESCRIPTION as STATUS,

                    EDUCATIONALHISTORYSTATUS.DESCRIPTION as STATUS,
                    case when @USEACADEMICCATALOG = 1 and EDUCATIONALINSTITUTION.ISAFFILIATED = 1 
                    then ACADEMICCATALOGDEGREE.NAME
                    else EDUCATIONALDEGREECODE.DESCRIPTION
                  end as DEGREE,
                    EDUCATIONALHISTORY.STARTDATE,
                    EDUCATIONALHISTORY.ENDDATE,
                    (select top 1 MAJORCODE.DESCRIPTION from dbo.EDUCATIONALMAJOR as MAJOR inner join dbo.EDUCATIONALMAJORCODE as MAJORCODE on MAJOR.EDUCATIONALMAJORCODEID = MAJORCODE.ID where MAJOR.EDUCATIONALHISTORYID = EDUCATIONALHISTORY.ID) as MAJOR,
                    (select top 1 MAJORCODE.DESCRIPTION from dbo.EDUCATIONALMINOR as MINOR inner join dbo.EDUCATIONALMAJORCODE as MAJORCODE on MINOR.EDUCATIONALMAJORCODEID = MAJORCODE.ID where MINOR.EDUCATIONALHISTORYID = EDUCATIONALHISTORY.ID) as MINOR,
                    EDUCATIONALHISTORY.GPA, 
                    EDUCATIONALHISTORY.CLASSOF,
                    EDUCATIONALHISTORY.CONSTITUENCYSTATUSCODE,
                    EDUCATIONALHISTORY.KNOWNNAME,
                    EDUCATIONALHISTORY.FRATERNITY,
                    dbo.UFN_EDUCATIONALHISTORYLEVELCODE_GETDESCRIPTION(EDUCATIONALHISTORY.EDUCATIONALHISTORYLEVELCODEID) as LEVEL,
                    dbo.UFN_EDUCATIONALHISTORYREASONCODE_GETDESCRIPTION(EDUCATIONALHISTORY.EDUCATIONALHISTORYREASONCODEID) as REASON,
                    dbo.UFN_EDUCATIONALPROGRAMCODE_GETDESCRIPTION(EDUCATIONALHISTORY.EDUCATIONALPROGRAMCODEID) as EDUCATIONALPROGRAM,
                    dbo.UFN_EDUCATIONALAWARDCODE_GETDESCRIPTION(EDUCATIONALHISTORY.EDUCATIONALAWARDCODEID) as EDUCATIONALAWARD,
                    EDUCATIONALHISTORY.DATELEFT,
                    EDUCATIONALHISTORY.DATEGRADUATED,
                    EDUCATIONALHISTORY.COMMENT,
                    EDUCATIONALHISTORY.PREFERREDCLASSYEAR,
                    EDUCATIONALHISTORY.ID as EDUCATIONALHISTORYID,
          EDUCATIONALINSTITUTION.ISAFFILIATED AS ISAFFILIATED 

                from
                    dbo.CONSTITUENT
                inner join
                    dbo.EDUCATIONALHISTORY on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
                inner join
                    dbo.BBNCEDUCATIONALHISTORYIDMAP on EDUCATIONALHISTORY.ID = BBNCEDUCATIONALHISTORYIDMAP.EDUCATIONALHISTORYID
                inner join 
                    dbo.EDUCATIONALINSTITUTION on EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
                inner join
                    dbo.EDUCATIONALHISTORYSTATUS on EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSID = EDUCATIONALHISTORYSTATUS.ID
                left join
                    dbo.EDUCATIONALHISTORYTYPECODE on EDUCATIONALHISTORY.EDUCATIONALHISTORYTYPECODEID = EDUCATIONALHISTORYTYPECODE.ID
                --left join

                --    dbo.EDUCATIONALHISTORYSTATUSCODE on EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSCODEID = EDUCATIONALHISTORYSTATUSCODE.ID

                left join
                    dbo.EDUCATIONALDEGREECODE on EDUCATIONALHISTORY.EDUCATIONALDEGREECODEID = EDUCATIONALDEGREECODE.ID
        left join 
                dbo.ACADEMICCATALOGDEGREE on EDUCATIONALHISTORY.ACADEMICCATALOGDEGREEID = ACADEMICCATALOGDEGREE.ID
                where 
                    CONSTITUENT.SEQUENCEID = @ID
                    and ISPRIMARYRECORD = 1;