USP_DATALIST_DUPLICATEEDUCATIONALHISTORYRESOLUTIONFIELDS

CREATE procedure dbo.USP_DATALIST_DUPLICATEEDUCATIONALHISTORYRESOLUTIONFIELDS
(
    @EDUCATIONALINSTITUTIONID uniqueidentifier,
    @EDUCATIONALPROGRAMID uniqueidentifier,
    @EDUCATIONALDEGREEID uniqueidentifier,
    @EDUCATIONALCOLLEGEID uniqueidentifier,
    @EDUCATIONALDEPARTMENTID uniqueidentifier,
    @EDUCATIONALDEGREETYPEID uniqueidentifier,
    @ACADEMICCATALOGDEGREEID uniqueidentifier,
    @ACADEMICCATALOGPROGRAMID uniqueidentifier,
    @ACADEMICCATALOGCOLLEGEID uniqueidentifier,
    @ACADEMICCATALOGDEPARTMENTID uniqueidentifier,
    @ACADEMICCATALOGDEGREETYPEID uniqueidentifier,
    @EDUCATIONALDIVISIONID uniqueidentifier = null,
    @EDUCATIONALSUBDEPARTMENTID uniqueidentifier = null,
    @ACADEMICCATALOGDIVISIONID uniqueidentifier = null,
    @ACADEMICCATALOGSUBDEPARTMENTID uniqueidentifier = null
)
as
    set nocount on;

    select
        EI.NAME as INSTITUTION,
        case
            when ECON.USEACADEMICCATALOG = 1 and EI.ISAFFILIATED = 1 then ACP.PROGRAM
            else EPC.DESCRIPTION 
        end as PROGRAM,
        case
            when ECON.USEACADEMICCATALOG = 1 and EI.ISAFFILIATED = 1 then ACDEG.NAME
            else EDC.DESCRIPTION 
        end as DEGREE,
        case 
            when ECON.USEACADEMICCATALOG = 1 and EI.ISAFFILIATED = 1 then ACC.NAME
            else ECC.DESCRIPTION 
        end as COLLEGE,
        case
            when ECON.USEACADEMICCATALOG = 1 and EI.ISAFFILIATED = 1 then ACDIV.NAME
            else EDIVC.DESCRIPTION 
        end as DIVISION,
        case
            when ECON.USEACADEMICCATALOG = 1 and EI.ISAFFILIATED = 1 then ACD.NAME
            else EDEPTC.DESCRIPTION 
        end as DEPARTMENT,
        case
            when ECON.USEACADEMICCATALOG = 1 and EI.ISAFFILIATED = 1 then ACSUB.NAME
            else ESUBDEPTC.DESCRIPTION 
        end as SUBDEPARTMENT,
        case
            when ECON.USEACADEMICCATALOG = 1 and EI.ISAFFILIATED = 1 then ACDTC.DESCRIPTION
            else EDTC.DESCRIPTION 
        end as DEGREETYPE
    from
        dbo.EDUCATIONALINSTITUTION EI
    left join
        dbo.EDUCATIONALPROGRAMCODE EPC on EPC.ID = @EDUCATIONALPROGRAMID
    left join
        dbo.EDUCATIONALDEGREECODE EDC on EDC.ID = @EDUCATIONALDEGREEID
    left join
        dbo.EDUCATIONALCOLLEGECODE ECC on ECC.ID = @EDUCATIONALCOLLEGEID
    left join
        dbo.EDUCATIONALDIVISIONCODE EDIVC on EDIVC.ID = @EDUCATIONALDIVISIONID
    left join
        dbo.EDUCATIONALDEPARTMENTCODE EDEPTC on EDEPTC.ID = @EDUCATIONALDEPARTMENTID
    left join
        dbo.EDUCATIONALSUBDEPARTMENTCODE ESUBDEPTC on ESUBDEPTC.ID = @EDUCATIONALSUBDEPARTMENTID
    left join
        dbo.EDUCATIONALDEGREETYPECODE EDTC on EDTC.ID = @EDUCATIONALDEGREETYPEID
    left join
        dbo.ACADEMICCATALOGPROGRAM ACP on ACP.ID = @ACADEMICCATALOGPROGRAMID
    left join
        dbo.ACADEMICCATALOGDEGREE ACDEG on ACDEG.ID = @ACADEMICCATALOGDEGREEID
    left join
        dbo.ACADEMICCATALOGCOLLEGE ACC on ACC.ID = @ACADEMICCATALOGCOLLEGEID
    left join
        dbo.ACADEMICCATALOGDIVISION ACDIV on ACDIV.ID = @ACADEMICCATALOGDIVISIONID
    left join
        dbo.ACADEMICCATALOGDEPARTMENT ACD on ACD.ID = @ACADEMICCATALOGDEPARTMENTID
    left join
        dbo.ACADEMICCATALOGSUBDEPARTMENT ACSUB on ACSUB.ID = @ACADEMICCATALOGSUBDEPARTMENTID
    left join
        dbo.ACADEMICCATALOGDEGREETYPE ACDT on ACDT.ID = @ACADEMICCATALOGDEGREETYPEID
    left join
        dbo.ACADEMICCATALOGDEGREETYPECODE ACDTC on ACDTC.ID = ACDT.ACADEMICCATALOGDEGREETYPECODEID
    left join
        dbo.EDUCATIONALCONFIGURATION ECON on 1 = 1
    where EI.ID = @EDUCATIONALINSTITUTIONID