USP_SEARCHLIST_EDUCATIONALHISTORYBATCHINDIVIDUALDUPLICATELOOKUPID

CREATE procedure dbo.USP_SEARCHLIST_EDUCATIONALHISTORYBATCHINDIVIDUALDUPLICATELOOKUPID
(
    @PRIMARYRECORDID uniqueidentifier = null,
    @PRIMARYCONTEXTRECORDID uniqueidentifier = null,
    @CONSTITUENTLOOKUPID nvarchar(100) = null,
    @CONSTITUENTKEYNAME nvarchar(100) = null,
    @CONSTITUENTFIRSTNAME nvarchar(50) = null,
    @EDUCATIONALINSTITUTIONID uniqueidentifier = null,
    @EDUCATIONALINSTITUTIONNAME nvarchar(100) = null,
    @EDUCATIONALDEGREECODEID uniqueidentifier = null,
    @EDUCATIONALPROGRAMCODEID uniqueidentifier = null,
    @ACADEMICCATALOGDEGREEID uniqueidentifier = null,
    @ACADEMICCATALOGPROGRAMID uniqueidentifier = null,
    @MAXROWS smallint = 500
)
as
    set nocount on;

    -- Ignore search when a educational history has been selected.

    if @PRIMARYRECORDID is null
    begin
        if @EDUCATIONALINSTITUTIONID is null
            select @EDUCATIONALINSTITUTIONID = ID from dbo.EDUCATIONALINSTITUTION where NAME = @EDUCATIONALINSTITUTIONNAME;

        select top(@MAXROWS)
            EH.ID,
            C.NAME,
            C.LOOKUPID,
            EI.NAME EDUCATIONALINSTITUTIONNAME,
            case 
                when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACD.NAME
                else EDC.DESCRIPTION 
            end as DEGREE,
            case
                when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACP.PROGRAM 
                else EPC.DESCRIPTION 
            end as PROGRAM,
            case
                when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACC.NAME
                else ECC.DESCRIPTION
            end as COLLEGE,
            case
                when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACDIV.NAME
                else EDIV.DESCRIPTION
            end as DIVISION,
            case
                when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACDEPT.NAME
                else EDEPT.DESCRIPTION
            end as DEPARTMENT,
            case
                when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACSUBDEPT.NAME
                else ESUBDEPT.DESCRIPTION
            end as SUBDEPARTMENT,
            case
                when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACDTC.DESCRIPTION
                else EDTC.DESCRIPTION
            end as DEGREETYPE
        from
            dbo.EDUCATIONALHISTORY EH
        join
            dbo.CONSTITUENT C on C.ID = EH.CONSTITUENTID
        left join
            dbo.EDUCATIONALINSTITUTION EI on EI.ID = EH.EDUCATIONALINSTITUTIONID
        left join
            dbo.EDUCATIONALDEGREECODE EDC on EDC.ID = EH.EDUCATIONALDEGREECODEID
        left join
            dbo.EDUCATIONALPROGRAMCODE EPC on EPC.ID = EH.EDUCATIONALPROGRAMCODEID
        left join
            dbo.ACADEMICCATALOGDEGREE ACD on ACD.ID = EH.ACADEMICCATALOGDEGREEID
        left join
            dbo.ACADEMICCATALOGPROGRAM ACP on ACP.ID = EH.ACADEMICCATALOGPROGRAMID
        left join
            dbo.EDUCATIONADDITIONALINFORMATION EAI on EAI.ID = (select top(1) ID from dbo.EDUCATIONADDITIONALINFORMATION where EDUCATIONALHISTORYID = EH.ID order by TSLONG desc)
        left join
            dbo.EDUCATIONALCOLLEGECODE ECC on ECC.ID = EAI.EDUCATIONALCOLLEGECODEID
        left join
            dbo.EDUCATIONALDIVISIONCODE EDIV on EDIV.ID = EAI.EDUCATIONALDIVISIONCODEID
        left join
            dbo.EDUCATIONALDEPARTMENTCODE EDEPT on EDEPT.ID = EAI.EDUCATIONALDEPARTMENTCODEID
        left join
            dbo.EDUCATIONALSUBDEPARTMENTCODE ESUBDEPT on ESUBDEPT.ID = EAI.EDUCATIONALSUBDEPARTMENTCODEID
        left join
            dbo.EDUCATIONALDEGREETYPECODE EDTC on EDTC.ID = EAI.EDUCATIONALDEGREETYPECODEID
        left join
            dbo.ACADEMICCATALOGCOLLEGE ACC on ACC.ID = EAI.ACADEMICCATALOGCOLLEGEID
        left join
            dbo.ACADEMICCATALOGDIVISION ACDIV on ACDIV.ID = EAI.ACADEMICCATALOGDIVISIONID
        left join
            dbo.ACADEMICCATALOGDEPARTMENT ACDEPT on ACDEPT.ID = EAI.ACADEMICCATALOGDEPARTMENTID
        left join
            dbo.ACADEMICCATALOGSUBDEPARTMENT ACSUBDEPT on ACSUBDEPT.ID = EAI.ACADEMICCATALOGSUBDEPARTMENTID
        left join
            dbo.ACADEMICCATALOGDEGREETYPE ACDT on ACDT.ID = EAI.ACADEMICCATALOGDEGREETYPEID
        left join
            dbo.ACADEMICCATALOGDEGREETYPECODE ACDTC on ACDTC.ID = ACDT.ACADEMICCATALOGDEGREETYPECODEID
        left join
            dbo.EDUCATIONALCONFIGURATION ECON on 1 = 1
        where
            EH.CONSTITUENTID in (select * from dbo.UFN_CONSTITUENT_GETIDSFROMCRITERIA( @PRIMARYCONTEXTRECORDID, @CONSTITUENTLOOKUPID, @CONSTITUENTKEYNAME, @CONSTITUENTFIRSTNAME, @MAXROWS))
            and 
            (
                (
                    (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) <> 1 
                    and
                    (
                        (EH.EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID
                        and EH.EDUCATIONALDEGREECODEID = @EDUCATIONALDEGREECODEID
                        and EH.EDUCATIONALPROGRAMCODEID = @EDUCATIONALPROGRAMCODEID)
                        or
                        (EH.EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID
                        and EH.EDUCATIONALDEGREECODEID = @EDUCATIONALDEGREECODEID
                        and @EDUCATIONALPROGRAMCODEID is null)
                        or
                        (EH.EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID
                        and @EDUCATIONALDEGREECODEID is null
                        and EH.EDUCATIONALPROGRAMCODEID = @EDUCATIONALPROGRAMCODEID)
                        or
                        (EH.EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID
                        and @EDUCATIONALDEGREECODEID is null
                        and @EDUCATIONALPROGRAMCODEID is null)
                    )
                )
                or
                (
                    (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 
                    and
                    (
                        (EH.EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID
                        and EH.ACADEMICCATALOGDEGREEID = @ACADEMICCATALOGDEGREEID
                        and EH.ACADEMICCATALOGPROGRAMID = @ACADEMICCATALOGPROGRAMID)
                        or
                        (EH.EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID
                        and EH.ACADEMICCATALOGDEGREEID = @ACADEMICCATALOGDEGREEID
                        and @ACADEMICCATALOGPROGRAMID is null)
                        or
                        (EH.EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID
                        and @ACADEMICCATALOGDEGREEID is null
                        and EH.ACADEMICCATALOGPROGRAMID = @ACADEMICCATALOGPROGRAMID)
                        or
                        (EH.EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID
                        and @ACADEMICCATALOGDEGREEID is null
                        and @ACADEMICCATALOGPROGRAMID is null)
                    )
                )        
            )
        order by
            C.KEYNAME asc, C.FIRSTNAME asc;
    end