UFN_BBNC_CONSTITEDUCATIONADDITIONALINFODATA

create function dbo.UFN_BBNC_CONSTITEDUCATIONADDITIONALINFODATA(
@EDUCATIONALHISTORYID uniqueidentifier, 
@IncludeUnaffiliated bit
)
returns @TABLE table
(
    EDUCATIONALHISTORYID uniqueidentifier,
    COLLEGE  nvarchar(200),
    DEGREETYPE  nvarchar(200),
    DIVISION  nvarchar(200),
    DEPARTMENT nvarchar(200),
    SUBDEPARTMENT nvarchar(200)
)
as
begin

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

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

  insert into @TABLE(
    EDUCATIONALHISTORYID,
    COLLEGE,
    DEGREETYPE,
    DIVISION,
    DEPARTMENT,
    SUBDEPARTMENT
  )
  select
        EH.ID,
        ACADEMICCATALOGCOLLEGE.NAME,  
        ACADEMICCATALOGDEGREETYPECODE.Description,
        ACADEMICCATALOGDIVISION.NAME,
        ACADEMICCATALOGDEPARTMENT.NAME,
        ACADEMICCATALOGSUBDEPARTMENT.NAME                
    from
        dbo.EDUCATIONALHISTORY EH
    inner join dbo.EDUCATIONALINSTITUTION on EH.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
    left join dbo.EDUCATIONADDITIONALINFORMATION  AI on AI.EDUCATIONALHISTORYID = EH.ID
    left join dbo.ACADEMICCATALOGPROGRAM on EH.ACADEMICCATALOGPROGRAMID = ACADEMICCATALOGPROGRAM.ID
    left join dbo.ACADEMICCATALOGCOLLEGE on AI.ACADEMICCATALOGCOLLEGEID = ACADEMICCATALOGCOLLEGE.ID
    left join dbo.ACADEMICCATALOGDIVISION on AI.ACADEMICCATALOGDIVISIONID = ACADEMICCATALOGDIVISION.ID
    left join dbo.ACADEMICCATALOGDEPARTMENT on AI.ACADEMICCATALOGDEPARTMENTID = ACADEMICCATALOGDEPARTMENT.ID
    left join dbo.ACADEMICCATALOGDEGREE on EH.ACADEMICCATALOGDEGREEID = ACADEMICCATALOGDEGREE.ID
    left join dbo.ACADEMICCATALOGDEGREETYPE on AI.ACADEMICCATALOGDEGREETYPEID = ACADEMICCATALOGDEGREETYPE.ID
    left join dbo.ACADEMICCATALOGDEGREETYPECODE on ACADEMICCATALOGDEGREETYPE.ACADEMICCATALOGDEGREETYPECODEID = ACADEMICCATALOGDEGREETYPECODE.ID
    left join dbo.ACADEMICCATALOGSUBDEPARTMENT on AI.ACADEMICCATALOGSUBDEPARTMENTID = ACADEMICCATALOGSUBDEPARTMENT.ID
    where
    EH.ID = @EDUCATIONALHISTORYID
        and coalesce(EDUCATIONALINSTITUTION.ISAFFILIATED,1) = 1  --Want to coalesce this to 1 in case the value is null to keep from excluding the wrong data

    and @USEACADEMICCATALOG = 1

    union all

    select
        EH.ID,
        EDUCATIONALCOLLEGECODE.DESCRIPTION,  
        EDUCATIONALDEGREETYPECODE.DESCRIPTION,
        EDUCATIONALDIVISIONCODE.DESCRIPTION,
        EDUCATIONALDEPARTMENTCODE.DESCRIPTION,
        EDUCATIONALSUBDEPARTMENTCODE.DESCRIPTION
    from
        dbo.EDUCATIONALHISTORY EH
    inner join dbo.EDUCATIONALINSTITUTION on EH.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
    left join dbo.EDUCATIONADDITIONALINFORMATION  AI on AI.EDUCATIONALHISTORYID = EH.ID
    left join dbo.EDUCATIONALPROGRAMCODE on EH.EDUCATIONALPROGRAMCODEID = EDUCATIONALPROGRAMCODE.ID
    left join dbo.EDUCATIONALCOLLEGECODE on AI.EDUCATIONALCOLLEGECODEID = EDUCATIONALCOLLEGECODE.ID
    left join dbo.EDUCATIONALDEPARTMENTCODE on AI.EDUCATIONALDEPARTMENTCODEID = EDUCATIONALDEPARTMENTCODE.ID
    left join dbo.EDUCATIONALDEGREECODE on EH.EDUCATIONALDEGREECODEID = EDUCATIONALDEGREECODE.ID
    left join dbo.EDUCATIONALDEGREETYPECODE on AI.EDUCATIONALDEGREETYPECODEID = EDUCATIONALDEGREETYPECODE.ID
    left join dbo.EDUCATIONALDIVISIONCODE on AI.EDUCATIONALDIVISIONCODEID = EDUCATIONALDIVISIONCODE.ID
    left join dbo.EDUCATIONALSUBDEPARTMENTCODE on AI.EDUCATIONALSUBDEPARTMENTCODEID = EDUCATIONALSUBDEPARTMENTCODE.ID 
    where 
        EH.ID = @EDUCATIONALHISTORYID
        and (@USEACADEMICCATALOG = 0 
    or (@IncludeUnaffiliated = 1 and coalesce(EDUCATIONALINSTITUTION.ISAFFILIATED,1) = 0 )) 

    return;
end