Bulk loading EPAFs using windEPAF

Despite what your Ellucian/SunGard Banner sales rep might have told you, bulk importing EPAFs from Excel is no easy process. Going straight from a spreadsheet into Banner is almost impossible to do. Actually multiple spreadsheets are involved because a single EPAF translates into dozens of records in three required tables (NOBTRAN, NORTRAN and NORROUT) and three optional tables (NORTERN, NORTLBD and NORCMNT). And the transaction number that ties all of these entries into a single record must be carefully maintained across all spreadsheets and must not conflict with EPAF users in INB and self-service that both write to these tables as well. And Banner allows users to define an infinite number of EPAF categories, each with its own definition of tables and columns it will write to and which fields are editable or defaulted in. It all adds up to a perfect configuration nightmare.

But you probably already knew that importing EPAFs from Excel was error prone and enormously difficult, otherwise you wouldn't be here reading this. So when my users told me they wanted to be able to import EPAFs from a single row on a spreadsheet, I knew I had an interesting challenge ahead. The first problem to address was the configuration issue. Rather than trying to configure a myriad of EPAF categories, we essentially eliminate configuration by using a reference EPAF as a template. So if you need faculty EPAFs with 1 informational, 1 apply and 2 approval records in NORROUT; 1 record in NORTERN, and up to 2 entries in NORTLBD then you just use an existing EPAF with those characteristics (or add a new one). The EPAFs you import will be just like your reference EPAF.

The next problem was how to look up values. When you are entering values on the spreadsheet, you need to know things like:

• are they already employees or do they need a current hire date?
• is the new record you are entering is their primary or secondary job?
• do they already have this job or is it a new job for them?

Making the users look up the value in Banner as they fill out the spreadsheet didn't feel like much of an improvement. So instead, users can simply enter special values when filling out the spreadsheet and the values will be looked up during import. Entering CV__ for any of the NORTRAN values will look up that employee's Current Values from either PEBEMPL, NBRBJOB or NBRJOBS. And if a value isn't found (the person is not a current employee or does not currently have that posn/suffix) then it tries to make a common sense decision about what the value should be. For instance if you enter __CV for pebempl_current_hire_date and the person is not currently an employee, then windEPAF will use the EPAF's effective date for the current hire date.

Users can also use DV__ to look up the Default Value that was set when the EPAF category was configured. The __CV and DV__ look-ups are only available in the NORTRAN section. A third translation, __RV (Reference Value) will look up the corresponding value in the reference EPAF.