USP_BBNC_CONSTITMAINDATA_FOR_GIVEN_CONSTITIDS

CREATE procedure [dbo].[USP_BBNC_CONSTITMAINDATA_FOR_GIVEN_CONSTITIDS](@IDs nvarchar(max) = '', @IncludeUnaffiliated bit = 0)
as begin

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

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

    declare @IDS_TABLE table (
        SEQUENCEID int,
        CONSTITUENTID uniqueidentifier
    );

    insert into
        @IDS_TABLE (SEQUENCEID)
    select
        ID
    from
        dbo.fnMakeIDsTableFromString(@IDs, ',');

    update
        @IDS_TABLE
    set
        CONSTITUENTID = C.ID
    from
        @IDS_TABLE IDT
    inner join
        dbo.CONSTITUENT C on C.SEQUENCEID = IDT.SEQUENCEID;

 --Adding CTE to better handle exclusion of unaffiliated data.  

with PRIMARYEDU_CTE as (
    select
        EH.ID as 'EduHistoryID',
        EDUCATIONALINSTITUTION.NAME,
        EH.STARTDATE,
        EH.DATELEFT,
        ACADEMICCATALOGDEGREE.NAME as 'Degree',            
        ACADEMICCATALOGPROGRAM.PROGRAM as 'Program',
        EH.CLASSOF        

    from
            @IDS_TABLE IDT
        inner join
            dbo.EDUCATIONALHISTORY EH on EH.CONSTITUENTID = IDT.CONSTITUENTID
    inner join dbo.EDUCATIONALINSTITUTION on EH.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
    left join dbo.ACADEMICCATALOGPROGRAM on EH.ACADEMICCATALOGPROGRAMID = ACADEMICCATALOGPROGRAM.ID
    left join dbo.ACADEMICCATALOGDEGREE on EH.ACADEMICCATALOGDEGREEID = ACADEMICCATALOGDEGREE.ID
    left join dbo.SCHOOL on EDUCATIONALINSTITUTION.ID = SCHOOL.ID
    where
  EH.ISPRIMARYRECORD = 1
        and not SCHOOL.ID is null --A record in the school table indicates that this institution is affiliated

    and @USEACADEMICCATALOG = 1

    union all

    select
        EH.ID as 'EduHistoryID',
        EDUCATIONALINSTITUTION.NAME,
        EH.STARTDATE,
        EH.DATELEFT,  
        EDUCATIONALDEGREECODE.DESCRIPTION as 'Degree',            
        EDUCATIONALPROGRAMCODE.DESCRIPTION as 'Program',
        EH.CLASSOF
    from
            @IDS_TABLE IDT
        inner join
            dbo.EDUCATIONALHISTORY EH on EH.CONSTITUENTID = IDT.CONSTITUENTID
    inner join dbo.EDUCATIONALINSTITUTION on EH.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
    left join dbo.EDUCATIONALPROGRAMCODE on EH.EDUCATIONALPROGRAMCODEID = EDUCATIONALPROGRAMCODE.ID
    left join dbo.EDUCATIONALDEGREECODE on EH.EDUCATIONALDEGREECODEID = EDUCATIONALDEGREECODE.ID
  left join dbo.SCHOOL on EDUCATIONALINSTITUTION.ID = SCHOOL.ID
    where 
        EH.ISPRIMARYRECORD = 1
    and not SCHOOL.ID is null
        and @USEACADEMICCATALOG = 0

    union all

  select
        EH.ID as 'EduHistoryID',
        EDUCATIONALINSTITUTION.NAME,
        EH.STARTDATE,
        EH.DATELEFT,  
        EDUCATIONALDEGREECODE.DESCRIPTION as 'Degree',            
        EDUCATIONALPROGRAMCODE.DESCRIPTION as 'Program',
        EH.CLASSOF
    from
            @IDS_TABLE IDT
        inner join
            dbo.EDUCATIONALHISTORY EH on EH.CONSTITUENTID = IDT.CONSTITUENTID
    inner join dbo.EDUCATIONALINSTITUTION on EH.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
    left join dbo.EDUCATIONALPROGRAMCODE on EH.EDUCATIONALPROGRAMCODEID = EDUCATIONALPROGRAMCODE.ID
    left join dbo.EDUCATIONALDEGREECODE on EH.EDUCATIONALDEGREECODEID = EDUCATIONALDEGREECODE.ID
    left join dbo.SCHOOL on EDUCATIONALINSTITUTION.ID = SCHOOL.ID
    where 
        EH.ISPRIMARYRECORD = 1
        and @IncludeUnaffiliated = 1 and
        SCHOOL.ID is null and --No record in the school table indicates that this institution is *not* affiliated

        @USEACADEMICCATALOG = 0

    union all

    select
        EH.ID as 'EduHistoryID',
        EDUCATIONALINSTITUTION.NAME,
        EH.STARTDATE,
        EH.DATELEFT,  
        EDUCATIONALDEGREECODE.DESCRIPTION as 'Degree',            
        EDUCATIONALPROGRAMCODE.DESCRIPTION as 'Program',
        EH.CLASSOF
    from
            @IDS_TABLE IDT
        inner join
            dbo.EDUCATIONALHISTORY EH on EH.CONSTITUENTID = IDT.CONSTITUENTID
    inner join dbo.EDUCATIONALINSTITUTION on EH.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
    left join dbo.EDUCATIONALPROGRAMCODE on EH.EDUCATIONALPROGRAMCODEID = EDUCATIONALPROGRAMCODE.ID
    left join dbo.EDUCATIONALDEGREECODE on EH.EDUCATIONALDEGREECODEID = EDUCATIONALDEGREECODE.ID
    left join dbo.SCHOOL on EDUCATIONALINSTITUTION.ID = SCHOOL.ID
    where 
        EH.ISPRIMARYRECORD = 1
        and @IncludeUnaffiliated = 1 and
        SCHOOL.ID is null and --No record in the school table indicates that this institution is *not* affiliated

        @USEACADEMICCATALOG = 1
)  
  select
  -- NOTE: DO NOT change the output column names of this select statement

  --bio

  CONSTITUENT.SEQUENCEID as ConstitSeqID,
  CONSTITUENT.LOOKUPID as ConstitLookupID,
  CONSTITUENT.FIRSTNAME as ConstitFirst,
  CONSTITUENT.MIDDLENAME as ConstitMiddle,
  CONSTITUENT.KEYNAME as ConstitLastOrg,
  CONSTITUENT.NAME as ConstitFull,
  CONSTITUENT.MAIDENNAME as ConstitMaiden,
  CONSTITUENT.NICKNAME as ConstitNickname,
  CONSTITUENT.BIRTHDATE as ConstitBirth,
  CONSTITGENDER.DESCRIPTION as ConstitGender,
  CONSTITTITLE.DESCRIPTION as ConstitTitle,
  CONSTITSUFFIX.DESCRIPTION as ConstitSuffix,
  case
    when (dbo.UFN_CONSTITUENT_GETREQUESTNOEMAIL(CONSTITUENT.SEQUENCEID) = 0)
    then 'No'
    else 'Yes'
  end as [ConstitRequestNoEmail],
  MARITALSTATUSCODE.DESCRIPTION as ConstitMaritalStatus,
  CONSTITUENT.WEBADDRESS as ConstitWebAddr,
  case
    when (CONSTITUENT.ISORGANIZATION = 1)
    then 'Organization' 
    else 'Individual'
  end as ConstitIsOrg,
  ORGCONSTITINDUSTRY.DESCRIPTION as ConstitIndustry,
  ORGCONSTIT.NUMEMPLOYEES as ConstitNumEmp,
  ORGCONSTIT.NUMSUBSIDIARIES as ConstitNumSubsid,
  case when DECEASEDCONSTITUENT.ID is null then 'no' else 'yes' end as [ConstitDeceased],
  case when DECEASEDCONSTITUENT.DECEASEDDATE is null then '00000000' else DECEASEDCONSTITUENT.DECEASEDDATE end [ConstitDeceasedDate],
  --primary address

  CONSTITCOUNTRY.DESCRIPTION as PrimaryAddrCountryLong,
  CONSTITCOUNTRY.ABBREVIATION as PrimaryAddrCountryShort,
  CONSTITADDR.ADDRESSBLOCK as PrimaryAddrBlock,
  '' as PrimaryAddrBlockLine1,
  '' as PrimaryAddrBlockLine2,
  '' as PrimaryAddrBlockLine3,
  '' as PrimaryAddrBlockLine4,
  '' as PrimaryAddrBlockLine5,
  CONSTITADDR.CITY as PrimaryAddrCity,
  case
  --TMV 04/11/2007 CR272266-040907 BBNC expects the full description for New Zealand city, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.

  --TMV 04/13/2007 CR272671-041207 BBNC expects the full description for United Kingdom county, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.

  when
  (
  CONSTITCOUNTRY.COUNTRYADDRESSFORMATID = '959809FB-8FA7-4A19-888F-9951BD9B29D3' --United Kingdom

  or
  CONSTITCOUNTRY.COUNTRYADDRESSFORMATID = 'A3B050A5-E1C8-4E1B-99AE-40E9FCADA0BC' --New Zealand

  )
  then
  CONSTITSTATE.DESCRIPTION
  else
  CONSTITSTATE.ABBREVIATION
  end as PrimaryAddrState,
  '' as PrimaryAddrNZCity,
  '' as PrimaryAddrNZSuburb,
  CONSTITADDR.POSTCODE as PrimaryAddrZip,
  CONSTITCOUNTRY.COUNTRYADDRESSFORMATID as PrimaryAddrCountryAddrFormatID,
  CONSTITREGIONCODE.[DESCRIPTION] as PrimaryAddrRegion,
  --spouse

  SPOUSETITLE.DESCRIPTION as SpouseTitle,
  SPOUSE.FIRSTNAME as SpouseFirst,
  SPOUSE.MIDDLENAME as SpouseMiddle,
  SPOUSE.KEYNAME as SpouseLast,
  SPOUSE.MAIDENNAME as SpouseMaiden,
  SPOUSESUFFIX.DESCRIPTION as SpouseSuffix,
  SPOUSE.NAME as SpouseFull,
  SPOUSEEDUHIST.CLASSOF as SpouseClassOf,
  --primary business

  BUSINESS.NAME as [PrimaryBizName],

  -- Use JOBTITLE from dbo.RelationshipJobInfo (SHL)

  JOBINFO.JOBTITLE as [PrimaryBizPosition],

  PRIMARYBIZINDUSTRY.DESCRIPTION as [PrimaryBizIndustry],
  BIZCOUNTRY.DESCRIPTION as PrimaryBizCountryLong,
  BIZCOUNTRY.ABBREVIATION as PrimaryBizCountryShort,
  BIZADDRESS.ADDRESSBLOCK as PrimaryBizAddrBlock,
  '' as PrimaryBizBlockLine1,
  ''
 as PrimaryBizBlockLine2,
  '' as PrimaryBizBlockLine3,
  '' as PrimaryBizBlockLine4,
  '' as PrimaryBizBlockLine5,
  BIZADDRESS.CITY as PrimaryBizCity,
  case
  --TMV 04/11/2007 CR272266-040907 BBNC expects the full description for New Zealand city, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.

  --TMV 04/13/2007 CR272671-041207 BBNC expects the full description for United Kingdom county, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.

  when
  (
  BIZCOUNTRY.COUNTRYADDRESSFORMATID = '959809FB-8FA7-4A19-888F-9951BD9B29D3' --United Kingdom

  or
  BIZCOUNTRY.COUNTRYADDRESSFORMATID = 'A3B050A5-E1C8-4E1B-99AE-40E9FCADA0BC' --New Zealand

  )
  then
  BIZSTATE.DESCRIPTION
  else
  BIZSTATE.ABBREVIATION
  end as PrimaryBizState,
  '' as PrimaryBizNZCity,
  '' as PrimaryBizNZSuburb,
  BIZADDRESS.POSTCODE as PrimaryBizZip,
  BIZCOUNTRY.COUNTRYADDRESSFORMATID as PrimaryBizCountryAddrFormatID,
  BIZREGIONCODE.[DESCRIPTION] as PrimaryBizRegion,
  --primary alumni

  PRIMARYEDU_CTE.NAME as PrimaryAlumniSchoolName,
  EDUCATIONALHISTORYTYPECODE.DESCRIPTION as PrimaryAlumniSchoolType,
  EDUCATIONALHISTORYSTATUSCODE.DESCRIPTION as PrimaryAlumniStatus,
  PRIMARYEDU_CTE.Degree as 'PrimaryAlumniDegree',
  PRIMARYEDU_CTE.STARTDATE as PrimaryAlumniStartDate,
  CONSTITEDUHISTORY.ENDDATE as PrimaryAlumniEndDate,
  (select top 1 MAJORCODE.DESCRIPTION from dbo.EDUCATIONALMAJOR as MAJOR inner join dbo.EDUCATIONALMAJORCODE as MAJORCODE on MAJOR.EDUCATIONALMAJORCODEID = MAJORCODE.ID where MAJOR.EDUCATIONALHISTORYID = CONSTITEDUHISTORY.ID) as PrimaryAlumniMajorAndSubjOfStudy,
  (select top 1 MAJORCODE.DESCRIPTION from dbo.EDUCATIONALMINOR as MINOR inner join dbo.EDUCATIONALMAJORCODE as MAJORCODE on MINOR.EDUCATIONALMAJORCODEID = MAJORCODE.ID where MINOR.EDUCATIONALHISTORYID = CONSTITEDUHISTORY.ID) as PrimaryAlumniMinor,
  CONSTITEDUHISTORY.GPA as PrimaryAlumniGPA,
  PRIMARYEDU_CTE.CLASSOF PrimaryAlumniClassOf,
  CONSTITEDUHISTORY.CONSTITUENCYSTATUSCODE as PrimaryAlumniUnknown,
  CONSTITEDUHISTORY.KNOWNNAME as PrimaryAlumniKnownName,
  CONSTITEDUHISTORY.FRATERNITY as PrimaryAlumniFrat,
  --not in bbec

  CONSTITTITLE2.description as ConstitTitle2,
  CONSTITSUFFIX2.description as ConstitSuffix2,
  SPOUSETITLE2.DESCRIPTION as SpouseTitle2,
  SPOUSESUFFIX2.DESCRIPTION as SpouseSuffix2,
  TARGETCODE.DESCRIPTION as [OrgINCOME],
  INCOMECODE.DESCRIPTION as [OrgTARGET],

  -- Grab the job category via the proper UFN (SHL)

  dbo.UFN_JOBCATEGORYCODE_GETDESCRIPTION(JOBINFO.JOBCATEGORYCODEID) as PrimaryBizProfession,

  '' as PrimaryAlumniCampus,
  '' as PrimaryAlumniDateGraduated,
  '' as PrimaryAlumniDepartment,
  '' as PrimaryAlumniFaculty,
  PRIMARYEDU_CTE.DATELEFT as 'DateLeft',
  PRIMARYEDU_CTE.Program,
  CONSTITEDUHISTORY.ID as 'PrimaryEducationID'

  from
        dbo.CONSTITUENT
    inner join
        @IDS_TABLE as IDs on IDs.CONSTITUENTID = CONSTITUENT.ID and CONSTITUENT.ISGROUP=0
  left join
    dbo.DEMOGRAPHIC on CONSTITUENT.ID = DEMOGRAPHIC.ID
  left join
    dbo.TARGETCODE on DEMOGRAPHIC.TARGETCODEID = TARGETCODE.ID
  left join
    dbo.INCOMECODE on DEMOGRAPHIC.INCOMECODEID = INCOMECODE.ID
  left join
  dbo.TITLECODE as CONSTITTITLE on CONSTITUENT.TITLECODEID = CONSTITTITLE.ID
  left join
  dbo.SUFFIXCODE as CONSTITSUFFIX on CONSTITUENT.SUFFIXCODEID = CONSTITSUFFIX.ID
  left join
  dbo.TITLECODE as CONSTITTITLE2 on CONSTITUENT.TITLE2CODEID = CONSTITTITLE2.ID
  left join 
  dbo.GENDERCODE as CONSTITGENDER on CONSTITUENT.GENDERCODEID = CONSTITGENDER.ID
  left join
  dbo.SUFFIXCODE as CONSTITSUFFIX2 on CONSTITUENT.SUFFIX2CODEID = CONSTITSUFFIX2.ID
  left join
  dbo.MARITALSTATUSCODE 
on CONSTITUENT.MARITALSTATUSCODEID = MARITALSTATUSCODE.ID
  left join
  dbo.DECEASEDCONSTITUENT on CONSTITUENT.ID = DECEASEDCONSTITUENT.ID
  left join
  dbo.ADDRESS as CONSTITADDR on CONSTITUENT.ID = CONSTITADDR.CONSTITUENTID and CONSTITADDR.ISPRIMARY = 1
  left join
  dbo.COUNTRY as CONSTITCOUNTRY on CONSTITADDR.COUNTRYID = CONSTITCOUNTRY.ID
  left join
  dbo.STATE as CONSTITSTATE on CONSTITADDR.STATEID = CONSTITSTATE.ID
  left join
  dbo.ADDRESSVALIDATIONUPDATE as CONSTITADDRESSVALIDATIONUPDATE on CONSTITADDRESSVALIDATIONUPDATE.ID = CONSTITADDR.ID
  left join
  dbo.REGIONCODE as CONSTITREGIONCODE on CONSTITREGIONCODE.ID = CONSTITADDRESSVALIDATIONUPDATE.REGIONCODEID
  left join
  dbo.RELATIONSHIP as SPOUSERELATION
  on
  SPOUSERELATION.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and
  SPOUSERELATION.ISSPOUSE = 1

left join
  dbo.CONSTITUENT as SPOUSE on SPOUSERELATION.RECIPROCALCONSTITUENTID = SPOUSE.ID
  left join
  dbo.TITLECODE as SPOUSETITLE on SPOUSE.TITLECODEID = SPOUSETITLE.ID
  left join
  dbo.SUFFIXCODE as SPOUSESUFFIX on SPOUSE.SUFFIXCODEID = SPOUSESUFFIX.ID
  -- Bug # 383584 'SpouseTitle2 and Spousesuffix2 not appears in BBIS

    left join
  dbo.TITLECODE as SPOUSETITLE2 on SPOUSE.TITLE2CODEID = SPOUSETITLE2.ID
  left join
  dbo.SUFFIXCODE as SPOUSESUFFIX2 on SPOUSE.SUFFIX2CODEID = SPOUSESUFFIX2.ID
  left join
  dbo.EDUCATIONALHISTORY as SPOUSEEDUHIST on SPOUSE.ID = SPOUSEEDUHIST.CONSTITUENTID and SPOUSEEDUHIST.ISPRIMARYRECORD = 1
  left join
  dbo.RELATIONSHIP as PRIMBIZRELATION on CONSTITUENT.ISORGANIZATION = 0
  and CONSTITUENT.ID = PRIMBIZRELATION.RECIPROCALCONSTITUENTID and PRIMBIZRELATION.ISPRIMARYBUSINESS = 1
  left join
  dbo.CONSTITUENT as BUSINESS on PRIMBIZRELATION.RELATIONSHIPCONSTITUENTID = BUSINESS.ID


  -- BBIS PBI 361198 START; Hooking directory searching into Primary Employment fields (SHL) -----------------------------------------------------------------------------------


  -- Joining in RELATIONSHIPJOBINFO in order to get the correct job title (SHL)

  left join
  dbo.RELATIONSHIPJOBINFO as JOBINFO on JOBINFO.RELATIONSHIPSETID = PRIMBIZRELATION.RELATIONSHIPSETID
  -- SHL BBIS Bug 378679; Grabbing the top 1 job from RELATIONSHIPJOBINFO using the latest STARTDATE because:

  -- a) That's how it's done in CRM (see USP_DATALIST_EMPLOYMENTHISTORY)

  -- b) Because there is no unique relationship between an organization in dbo.RELATIONSHIP to its many potential jobs in RELATIONSHIPJOBINFO, multiple RELATIONSHIPJOBINFO rows will be returned which does not fly

  and 
  (select top 1 ID from dbo.RELATIONSHIPJOBINFO where RELATIONSHIPJOBINFO.RELATIONSHIPSETID = PRIMBIZRELATION.RELATIONSHIPSETID order by STARTDATE desc) = JOBINFO.ID

   -- Joining in ORGANIZATIONDATA once for constituents that are organizations as ORGCONSTIT

   left join
   dbo.ORGANIZATIONDATA as ORGCONSTIT on CONSTITUENT.ID = ORGCONSTIT.ID and CONSTITUENT.ISORGANIZATION = 1
   -- Joining in ORGANIZATIONDATA again for a constituent's primary business information as PRIMARYBIZORG

   left join
   dbo.ORGANIZATIONDATA as PRIMARYBIZORG on PRIMARYBIZORG.ID = PRIMBIZRELATION.RELATIONSHIPCONSTITUENTID

   -- Joining in INDUSTRYCODE once for constituents that are organizations as ORGCONSTITINDUSTRY

   left join
   dbo.INDUSTRYCODE as ORGCONSTITINDUSTRY on ORGCONSTIT.INDUSTRYCODEID = ORGCONSTITINDUSTRY.ID
   -- Joining in INDUSTRYCODE again for a constituent's primary business information as PRIMARYBIZINDUSTRY

   left join
   dbo.INDUSTRYCODE as PRIMARYBIZINDUSTRY on PRIMARYBIZORG.INDUSTRYCODEID = PRIMARYBIZINDUSTRY.ID

  -- BBIS PBI 361198 END; Hooking directory searching into Primary Employment fields ------------------------------------------------------------------------------------------


  --dbo.ADDRESS as BIZADDRESS on CONSTITUENT.ID = BIZADDRESS.CONSTITUENTID -- Pull the address from the constituent, not their business.

  left join
  dbo.ADDRESS as BIZADDRESS on PRIMBIZRELATION.RELATIONSHIPCONSTITUENTID = BIZADDRESS.CONSTITUENTID --Why pull the constit address as their business address?? wi-104903/109889

  --and BIZADDRESS.ADDRESSTYPECODEID = (select top 1 BUSINESSADDRESSTYPECODEID from dbo.NETCOMMUNITYDEFAULTCODEMAP)

  --and BIZADDRESS.RELATIONSHIPID = PRIMBIZRELATION.ID

  and BIZADDRESS.ISPRIMARY = 1
  left join
  dbo.COUNTRY as BIZCOUNTRY on BIZADDRESS.COUNTRYID = BIZCOUNTRY.ID
  left join
  dbo.STATE as BIZSTATE on BIZADDRESS.STATEID = BIZSTATE.ID
  left join
  dbo.ADDRESSVALIDATIONUPDATE as BIZADDRESSVALIDATIONUPDATE on BIZADDRESSVALIDATIONUPDATE.ID = BIZADDRESS.ID
  left join
  dbo.REGIONCODE as BIZREGIONCODE
 on BIZREGIONCODE.ID = BIZADDRESSVALIDATIONUPDATE.REGIONCODEID
  left join
  dbo.EDUCATIONALHISTORY as CONSTITEDUHISTORY on CONSTITUENT.ID = CONSTITEDUHISTORY.CONSTITUENTID and CONSTITEDUHISTORY.ISPRIMARYRECORD = 1
  left join
  dbo.EDUCATIONALINSTITUTION
 on CONSTITEDUHISTORY.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
  left join
  dbo.EDUCATIONALHISTORYTYPECODE on CONSTITEDUHISTORY.EDUCATIONALHISTORYTYPECODEID = EDUCATIONALHISTORYTYPECODE.ID
  left join
  dbo.EDUCATIONALHISTORYSTATUSCODE on CONSTITEDUHISTORY.EDUCATIONALHISTORYSTATUSCODEID = EDUCATIONALHISTORYSTATUSCODE.ID
  left join
    PRIMARYEDU_CTE on CONSTITEDUHISTORY.ID = PRIMARYEDU_CTE.EduHistoryID

  END