Banner Upgrade Issue - Student Email Accounts are created with invalid format

 Problem Description : 

Following a Banner upgrade, if the Gordon customizations for the f_generate_external_user function within the goktpty package are not put in, student AD accounts could get created with an invalid username/email address. This will also affect the Third Party ID in Banner GOATPAD that gets generated for students (which is what the email address is based on). Script was modifed on 7/16/19 so that the actual AD accounts will not get created if the username is invalid.

 Resolution : 

Following a Banner upgrade, if the Gordon customizations for the f_generate_external_user function within the goktpty package are not put in, student AD accounts could get created with an invalid username/email address. The student account provisioning script on Heron (\\heron\d$\batch\Banner\New Student Processing\email_new.sql) populates the third party ID for each student in GOATPAD using the goktpty.f_generate_external_user function and then creates the flat file that gets copied over to the Exchange Server for processing. The student's email address/username is created based on that third party id generated by the f_generate_external_user. The baseline of the package generates the format as first initial + last name. The function needs to generate our student format which is first initial + last initial + last 6 digits of the 929 #. The custom code in the function generates the correct format which is included below. ITS will need to apply the customization if it isnt there to correct the issue.  

FUNCTION F_Generate_External_User (PIDM IN NUMBER)

  RETURN VARCHAR2

  is

--  Function generates and verifies if a newly created userid already

--  exists.  If the new userid exists, the function will generate a 'one-up'

--  userid and verify if that one exists.  The function continues the

--  'one_up' userid generation until it finds a unique userid.

CURSOR c_select_length_last IS

        SELECT GUBPPRF_LENGTH_LAST_NAME

        FROM GUBPPRF;

CURSOR c_select_userid IS

    SELECT LOWER(SPRIDEN_FIRST_NAME), LOWER(SPRIDEN_LAST_NAME)

            FROM SPRIDEN

            WHERE SPRIDEN_ENTITY_IND = 'P'

            AND SPRIDEN_CHANGE_IND IS NULL

            AND SPRIDEN_PIDM = PIDM;

  new_userid  gobtpac.gobtpac_external_user%TYPE;

  orig_userid    gobtpac.gobtpac_external_user%TYPE;

  counter      NUMBER := 0;

  first_name  SPRIDEN.SPRIDEN_FIRST_NAME%TYPE;

  last_name    SPRIDEN.SPRIDEN_LAST_NAME%TYPE;

  lv_len_last  NUMBER(2);

BEGIN

  OPEN c_select_length_last;

  FETCH c_select_length_last INTO lv_len_last;

  CLOSE c_select_length_last;

  OPEN c_select_userid;

  FETCH c_select_userid INTO first_name, last_name;

  CLOSE c_select_userid;

  /*function, f_character_filter, removes all special characters from names*/

  last_name  := f_character_filter(gukcnva.f_convert(last_name));

  first_name := f_character_filter(gukcnva.f_convert(first_name));

-- gdn, 07/27/2007 - IF clause to generate student userids based on Gordon standard

-- original line below

--    orig_userid := SUBSTR(first_name,1,1)||SUBSTR(last_name,1,lv_len_last);

-- Gordon standard prior to SSN Conversion

--  orig_userid := substr(first_name,1,1)||substr(last_name,1,1)||

--  to_char(student.birthdate(pidm),'DDMM')||substr(student.ssn(pidm,'ND'),6,2);

   if f_student_admissions_ind(pidm) = 'Y' or f_finaid_applicant_ind(pidm,null,null) = 'Y'then

        if substr(student.id(pidm,'ND'),1,3) = '929' then

            orig_userid := substr(first_name,1,1)||substr(last_name,1,1)||

            substr(student.id(pidm,'ND'),4,6);

        else

            orig_userid := null;

        end if;

  else if f_student_faculty_ind(pidm) = 'Y' then

        orig_userid := SUBSTR(first_name,1,1)||last_name;

  else

        orig_userid := first_name||SUBSTR(last_name,1,1);

  end if;

  end if;

-- gdn end

  new_userid := orig_userid;

  WHILE new_userid = F_CHECK_EXTERNAL_USER(pidm, new_userid)

    LOOP

      counter := counter + 1;

    -- gdn - 07/27/2007 - Do not remove chars from userid as adding to make unique

  if f_student_admissions_ind(pidm) = 'Y' or f_finaid_applicant_ind(pidm,null,null) = 'Y' then

        new_userid := orig_userid||chr(counter+96);

  else

        new_userid := orig_userid||counter;

  end if;

/*

      if counter < 10 then

        new_userid := substr(orig_userid,1,lv_len_last)||TO_CHAR(counter);

      elsif counter < 100 then

        new_userid := substr(orig_userid,1,(lv_len_last - 1))||TO_CHAR(counter);

      elsif counter < 1000 then

        new_userid := substr(orig_userid,1,(lv_len_last - 2))||TO_CHAR(counter);

      elsif counter < 10000 then

        new_userid := substr(orig_userid,1,(lv_len_last - 3))||TO_CHAR(counter);

      else

        new_userid := substr(orig_userid,1,(lv_len_last - 4))||TO_CHAR(counter);

      end if;

*/

-- gdn end

    END LOOP;

-- gdn, 08/14/2007 - If a student and userid < 8 then return null.  Usually happens

-- when there is no password entered in SPAPERS so userid is only 4

  if (f_student_admissions_ind(pidm) = 'Y' or f_finaid_applicant_ind(pidm,null,null) = 'Y')

        and length(new_userid) < 8 then

        new_userid := null;

  end if;

-- gdn end

--

  RETURN new_userid;

END;

*************************

Any invalid third party ids can be identified by running the script below. These can be corrected in GOATPAD and then the script on Heron should create the student account during nightly processing.

 SELECT  student.id(PIDM, 'ND'), 

        student.name(PIDM), 

        ADMT_CODE,

        STUDENT.BIRTHDATE(PIDM),

        fg_external_user(PIDM),

        student.email_address(PIDM)

  FROM  v_apps va

 WHERE    

        va.TERM_CODE_ENTRY >= fg_current_term()

          AND SUBSTR (fg_external_user(PIDM), 3, 1) NOT IN

                    ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')

                    order by fg_external_user(PIDM)

 Revision Date : 7/16/2019