USP_BBNC_CONSTITEDUCATIONALHISTORYDATA

            CREATE procedure dbo.USP_BBNC_CONSTITEDUCATIONALHISTORYDATA(@ID int)
            as

                set NOCOUNT on;

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

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

                select 
                    EDUCATIONALHISTORY.ID,
                    EDUCATIONALINSTITUTION.NAME,
                    EDUCATIONALHISTORYTYPECODE.DESCRIPTION as SCHOOLTYPE,
                    EDUCATIONALHISTORYSTATUS.DESCRIPTION as STATUS,
                    EDUCATIONALHISTORYSTATUS.ID as STATUSID,
                    dbo.UFN_EDUCATIONALHISTORYLEVELCODE_GETDESCRIPTION(EDUCATIONALHISTORY.EDUCATIONALHISTORYLEVELCODEID) as LEVEL,
                    dbo.UFN_EDUCATIONALHISTORYREASONCODE_GETDESCRIPTION(EDUCATIONALHISTORY.EDUCATIONALHISTORYREASONCODEID) as REASON,
                    dbo.UFN_EDUCATIONALPROGRAMCODE_GETDESCRIPTION(EDUCATIONALHISTORY.EDUCATIONALDEGREECODEID) as DEGREE,

                    -- SHL BBIS Bug 372613; When academic catalog is on and the institution is affiliated, we need to grab the program

          -- from dbo.ACADEMICCATALOGPROGRAMCODE instead of dbo.EDUCATIONALHISTORY

                    case when @USEACADEMICCATALOG = 1 and EDUCATIONALINSTITUTION.ISAFFILIATED = 1 then
                        ACADEMICCATALOGPROGRAM.PROGRAM
                    else
                        dbo.UFN_EDUCATIONALPROGRAMCODE_GETDESCRIPTION(EDUCATIONALHISTORY.EDUCATIONALPROGRAMCODEID) 
                    end as EDUCATIONALPROGRAM,

                    case when @USEACADEMICCATALOG = 1 and EDUCATIONALINSTITUTION.ISAFFILIATED = 1 then 
                        ACADEMICCATALOGDEGREE.NAME
                    else dbo.UFN_EDUCATIONALDEGREECODE_GETDESCRIPTION(EDUCATIONALHISTORY.EDUCATIONALDEGREECODEID)
                      end as EDUCATIONALDEGREE,

                    dbo.UFN_EDUCATIONALAWARDCODE_GETDESCRIPTION(EDUCATIONALHISTORY.EDUCATIONALAWARDCODEID) as EDUCATIONALAWARD,
                    EDUCATIONALHISTORY.STARTDATE,
                    EDUCATIONALHISTORY.DATELEFT,
                    EDUCATIONALHISTORY.DATEGRADUATED,
                    EDUCATIONALHISTORY.COMMENT,
                    --EDUCATIONALMAJORCODE.DESCRIPTION as MAJOR,

                    (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.PREFERREDCLASSYEAR,
                    EDUCATIONALHISTORY.CONSTITUENCYSTATUSCODE,
                    EDUCATIONALHISTORY.KNOWNNAME,
                    EDUCATIONALHISTORY.FRATERNITY,
                    EDUCATIONALHISTORY.ISPRIMARYRECORD,
                    EDUCATIONALINSTITUTION.ISAFFILIATED ISSCHOOLAFFILIATED
                from
                    dbo.CONSTITUENT
                inner join
                    dbo.EDUCATIONALHISTORY on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
                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.ACADEMICCATALOGDEGREE on EDUCATIONALHISTORY.ACADEMICCATALOGDEGREEID = ACADEMICCATALOGDEGREE.ID

              -- SHL BBIS Bug 372613; When academic catalog is on and the institution is affiliated, we need to grab the program

        -- from dbo.ACADEMICCATALOGPROGRAMCODE instead of dbo.EDUCATIONALHISTORY

              left join
              dbo.ACADEMICCATALOGPROGRAM on EDUCATIONALHISTORY.ACADEMICCATALOGPROGRAMID = ACADEMICCATALOGPROGRAM.ID

                  --left join

                    --dbo.EDUCATIONALMAJORCODE on EDUCATIONALHISTORY.EDUCATIONALMAJORCODEID = EDUCATIONALMAJORCODE.ID

                where 
                    CONSTITUENT.SEQUENCEID = @ID;