USP_BBNC_CONSTITEDUCATIONALINVOLVEMENTDATA

      CREATE procedure dbo.USP_BBNC_CONSTITEDUCATIONALINVOLVEMENTDATA(@ID int)
            as

                set NOCOUNT on;

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

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


                select 
          EDUCATIONALINVOLVEMENT.ID,
          EDUCATIONALINSTITUTION.NAME EDUCATIONALINSTITUTION,
          EDUCATIONALINVOLVEMENTTYPECODE.DESCRIPTION TYPE,
          EDUCATIONALINVOLVEMENT.DATEFROM,
          EDUCATIONALINVOLVEMENT.DATETO,
          EDUCATIONALINVOLVEMENTNAME.NAME,
          EDUCATIONALINVOLVEMENT.COMMENT,
          EDUCATIONALPROGRAMCODE.DESCRIPTION PROGRAM,
          EDUCATIONALCOLLEGECODE.DESCRIPTION COLLEGE,
          EDUCATIONALDIVISIONCODE.DESCRIPTION DIVISION,
          EDUCATIONALDEPARTMENTCODE.DESCRIPTION DEPARTMENT,
          EDUCATIONALSUBDEPARTMENTCODE.DESCRIPTION SUBDEPARTMENT,
          EDUCATIONALINVOLVEMENT.POSITION,
          EDUCATIONINVOLVEMENTAWARDCODE.DESCRIPTION AWARD,
          EDUCATIONALINSTITUTION.ISAFFILIATED ISAFFILIATED
                from
                    dbo.CONSTITUENT
                inner join
                    dbo.EDUCATIONALINVOLVEMENT on CONSTITUENT.ID = EDUCATIONALINVOLVEMENT.CONSTITUENTID
                left join 
                    dbo.EDUCATIONALINSTITUTION on EDUCATIONALINVOLVEMENT.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
        inner join
          dbo.EDUCATIONALINVOLVEMENTNAME on EDUCATIONALINVOLVEMENT.EDUCATIONALINVOLVEMENTNAMEID = EDUCATIONALINVOLVEMENTNAME.ID
                inner join
                    dbo.EDUCATIONALINVOLVEMENTTYPECODE on EDUCATIONALINVOLVEMENT.EDUCATIONALINVOLVEMENTTYPECODEID = EDUCATIONALINVOLVEMENTTYPECODE.ID
                left join
                    dbo.EDUCATIONALPROGRAMCODE on EDUCATIONALINVOLVEMENT.EDUCATIONALPROGRAMCODEID = EDUCATIONALPROGRAMCODE.ID
                left join
                    dbo.EDUCATIONALCOLLEGECODE on EDUCATIONALINVOLVEMENT.EDUCATIONALCOLLEGECODEID = EDUCATIONALCOLLEGECODE.ID
        left join
                    dbo.EDUCATIONALDIVISIONCODE on EDUCATIONALINVOLVEMENT.EDUCATIONALDIVISIONCODEID = EDUCATIONALDIVISIONCODE.ID
        left join
                    dbo.EDUCATIONALDEPARTMENTCODE on EDUCATIONALINVOLVEMENT.EDUCATIONALDEPARTMENTCODEID = EDUCATIONALDEPARTMENTCODE.ID
        left join
                    dbo.EDUCATIONALSUBDEPARTMENTCODE on EDUCATIONALINVOLVEMENT.EDUCATIONALSUBDEPARTMENTCODEID = EDUCATIONALSUBDEPARTMENTCODE.ID
        left join 
          dbo.EDUCATIONINVOLVEMENTAWARDCODE on EDUCATIONALINVOLVEMENT.EDUCATIONALINVOLVEMENTAWARDCODEID = EDUCATIONINVOLVEMENTAWARDCODE.ID
                where 
                    CONSTITUENT.SEQUENCEID = @ID
                    and
          -- SHL BBIS Bug 380610; If Academic Catalog is off then we still want to show an affiliated institution's info

          (
                      EDUCATIONALINSTITUTION.ISAFFILIATED = 0
                      or
                      (EDUCATIONALINSTITUTION.ISAFFILIATED = 1 and @USEACADEMICCATALOG = 0)
            -- SHL BBIS Bug 401584; Educational involvement should show even when an educational institution is not involved

            or
            EDUCATIONALINSTITUTION.ID is null
          )
          -- SHL BBIS Bug 388685; Including the affiliated institution information for institutes that are affiliated            

            union
          select 
            EDUCATIONALINVOLVEMENT.ID,
            EDUCATIONALINSTITUTION.NAME EDUCATIONALINSTITUTION,
            EDUCATIONALINVOLVEMENTTYPECODE.DESCRIPTION TYPE,
            EDUCATIONALINVOLVEMENT.DATEFROM,
            EDUCATIONALINVOLVEMENT.DATETO,
            EDUCATIONALINVOLVEMENTNAME.NAME,
            EDUCATIONALINVOLVEMENT.COMMENT,
            ACADEMICCATALOGPROGRAM.PROGRAM PROGRAM,
            ACADEMICCATALOGCOLLEGE.NAME COLLEGE,
            ACADEMICCATALOGDIVISION.NAME DIVISION,
            ACADEMICCATALOGDEPARTMENT.NAME DEPARTMENT,
            ACADEMICCATALOGSUBDEPARTMENT.NAME SUBDEPARTMENT,
            EDUCATIONALINVOLVEMENT.POSITION,
            EDUCATIONINVOLVEMENTAWARDCODE.DESCRIPTION AWARD,
            EDUCATIONALINSTITUTION.ISAFFILIATED ISAFFILIATED
                  from
                      dbo.CONSTITUENT
                  inner join
                      dbo.EDUCATIONALINVOLVEMENT on CONSTITUENT.ID = EDUCATIONALINVOLVEMENT.CONSTITUENTID
                  left join 
                      dbo.EDUCATIONALINSTITUTION on EDUCATIONALINVOLVEMENT.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
                  inner join
                      dbo.EDUCATIONALINVOLVEMENTNAME on EDUCATIONALINVOLVEMENT.EDUCATIONALINVOLVEMENTNAMEID = EDUCATIONALINVOLVEMENTNAME.ID
                  inner join
                      dbo.EDUCATIONALINVOLVEMENTTYPECODE on EDUCATIONALINVOLVEMENT.EDUCATIONALINVOLVEMENTTYPECODEID = EDUCATIONALINVOLVEMENTTYPECODE.ID
                  left join
                      dbo.ACADEMICCATALOGPROGRAM on EDUCATIONALINVOLVEMENT.ACADEMICCATALOGPROGRAMID = ACADEMICCATALOGPROGRAM.ID
                  left join
                      dbo.ACADEMICCATALOGCOLLEGE on EDUCATIONALINVOLVEMENT.ACADEMICCATALOGCOLLEGEID = ACADEMICCATALOGCOLLEGE.ID
                  left join
                      dbo.ACADEMICCATALOGDIVISION on EDUCATIONALINVOLVEMENT.ACADEMICCATALOGDIVISIONID = ACADEMICCATALOGDIVISION.ID
                  left join
                      dbo.ACADEMICCATALOGDEPARTMENT on EDUCATIONALINVOLVEMENT.ACADEMICCATALOGDEPARTMENTID = ACADEMICCATALOGDEPARTMENT.ID
                  left join
                      dbo.ACADEMICCATALOGSUBDEPARTMENT on EDUCATIONALINVOLVEMENT.ACADEMICCATALOGSUBDEPARTMENTID = ACADEMICCATALOGSUBDEPARTMENT.ID
          left join 
            dbo.EDUCATIONINVOLVEMENTAWARDCODE on EDUCATIONALINVOLVEMENT.EDUCATIONALINVOLVEMENTAWARDCODEID = EDUCATIONINVOLVEMENTAWARDCODE.ID
                  where 
                      CONSTITUENT.SEQUENCEID = @ID
                      and 
                      EDUCATIONALINSTITUTION.ISAFFILIATED = 1
                      -- SHL BBIS Bug 380610; Only want to show Academic Catalog information when Academic Catalog is on

                      and
                      @USEACADEMICCATALOG = 1;