Banner Extracts

1
Banner Extracts
2
Banner Extracts

• Purpose of the Banner Extracts
• Extract data from the Banner database that is
  required by DegreeWorks and load the data into
  the RAD, DAP and/or SHP databases.
• Banner versions currently supported 7 and 8

3
Banner Extracts
DegreeWorks Server
Banner Server
Course Equivalency
ETS Data and UCX
Course Data
Student Data
Extract
Real Time
Batch
Batch Requests
Web Requests
Transit/cron
Users
4
Banner Extracts

• Extract Process Flow
• Select desired student population
• SQL file creates file of IDs or
• Use Selection criteria to create file of IDs
• Run Banner Extract and Bridge (RAD30)
• Read mode (e.g. Student, Advisor )
• Process each ID (BAN40/banstudent)
• Read SPRIDEN (primary-mst)
• Read SORLCUR/SORLFOS (degree-dtl)
• Read SFRSTCR, SHRTCKN, SHRTRCE (class/transfer-dtl
  )
• Load (bridge) data (RAD41/radbridge)
• Hash used to check for data changes
• Generate Audit Processor (DAP22) called to
  process students with data changes

5

• Current Banner Extracts
• STUDENT All Student oriented data
• COURSE - Course data (rad_course_mst)
• EQUIV Equivalencies (dap_eqv_crs_mst/CFG070)
• UCX (only 15 tables are extracted into DW)
• ADVISOR/STAFF
• rad_primary_mst, shp_user_mst, dap_user_mst

6

• Order of Processing
• UCX load the UCX tables from Banner
• COURSE load the rad_course_mst
• EQUIV load the dap_eqv_crs_mst
• Must load UCX-CFG074 Reused Course Keys first
• STAFF load staff for PC products/web
• ADVISOR load the advisors into the
  rad_primary_mst, shp_user_mst, dap_user_mst
• STUDENT load few test students to start and
  then determine the pool of students that are
  desired in DegreeWorks

7
Banner Extracts
8

• Three ways to run an extract
• 1. Transit RAD30
• 2. Web On-demand refresh
• 3. Batch script from the command line or cron/at

9

• Extracting Student Data
• By Student
• On Demand
• Transit
• Select 1 student
• Script/cron job
• bannerextract student studentid
• Select a group of students
• Transit
• Use selection criteria
• Script/cron job
• bannerextract student idfile_of_student_ids.ids
• Defined SQL job
• Transit
• Checkbox Use Default SQL
• Script/cron job
• bannerextract student

10
(No Transcript)
11

• Batch RAD30JOB
• Access via Transit
• Banner Selection criteria only available for
  STUDENT
• BA02 - Student ID
• BCLG - College code
• BCON - Concentration codes
• BDEG - Degree code
• BLVL - Student Class Level code
• BMAJ - Major codes
• BMIN - Minor codes
• BSCH - Level code
• BSTS - Student type code
• BB05 - End term
• BB06 - Matric term
• BB07 - Admit term
• BB11 - Grad term
• BB12 - Academic cat-yr term

12

• Invoking Other Modes via Transit - RAD30
• COURSE ALWAYS extracts ALL records
• ADDs record if NOT found, otherwise it updates
  record
• EQUIV - ALWAYS extracts ALL records
• DELETES ALL dap_eqv_crs_mst RECORDS, then
  recalculates equivalencies from scratch and ADDs
  all new records (rewrites CFG070)
• UCX ALWAYS extracts ALL records
• Configuration flag in CFG020 BANNER Add UCX
  Only
• If Y then UCX records are only added if NOT
  found, otherwise the record is skipped.
• If N then the each UCX table extracted is
  first deleted and then added entirely from Banner

13
Banner Extracts

• On-demand Refresh
• Banner data is refreshed (button)
• SDREFRES key in the SHPCFG file
• Clicking button always performs refresh
• Timeout is ignored
• Hash checked but date still updated
• No audit is run
• Good for troubleshooting issues
• BC 16

14

• Invoking the Banner Extracts from the Command
  Line
• bannerextract extract_type ltid or sql filegt
• Extract_type
• student
• advisor
• staff
• course
• ucx
• Id file
• Example - student.ids
• SQL file
• If no ID or SQL file included, then default SQL
  file will be used

15

• Script IFAS_HOME/scripts/bannerextract
• The bannerextract command line script may be
  used to run any of the Banner extract programs
  instead of Transit/RAD30.
• This script MUST NOT BE RUN from the
  IFAS_HOME/sql directory (contains the local SQL
  files).
• The bannerextract sql just select the pool of
  records (e.g, selected by ID Codes, Course Keys)
  that will be extracted from Banner. They have
  nothing to do with the actual data being
  extracted.
• The ADMIN_HOME/common/bannerextract.config file
  is used to actually extract the data from the
  Banner database. More later on this topic

16

• Student
• To run the student extract using
  bannerstudents.sql file in the LOCAL_HOME/sql
  directory
• bannerextract student
• You may also specify a different sql file in the
  LOCAL_HOME/sql directory
• bannerextract student somestudents.sql
• You may also specify a file of student IDs in the
  LOCAL_HOME/sql or current directory
• bannerextract student somestudents.ids
• For testing purposes you may also supply a single
  student ID instead of a file name
• bannerextract student 1234567

17
Banner Extract Population

• Select active pool of students
• local/sql/bannerstudent.sql

SELECT DISTINCT(SPRIDEN_ID) FROM SPRIDEN, SGBSTDN
B WHERE B.SGBSTDN_STST_CODE IN (SELECT
STVSTST_CODE FROM STVSTST WHERE
STVSTST_REG_IND 'Y') AND
B.SGBSTDN_TERM_CODE_EFF (SELECT
MAX(C.SGBSTDN_TERM_CODE_EFF) FROM SGBSTDN C
WHERE C.SGBSTDN_TERM_CODE_EFF lt '200710'
AND C.SGBSTDN_PIDM B.SGBSTDN_PIDM
AND B.SGBSTDN_LEVL_CODE IN ('UG', 'GR', 'LA'))
AND B.SGBSTDN_TERM_CODE_CTLG_1 gt '200610'
AND SPRIDEN_PIDM B.SGBSTDN_PIDM AND
SPRIDEN_CHANGE_IND IS NULL ORDER BY SPRIDEN_ID
18

• STUDENT LOCAL_HOME/sql/bannerstudents.sql
• -- Change this select stmt to be whatever you
  want to select the pool
• -- of students you want pulled into DegreeWorks
• SELECT DISTINCT(SPRIDEN_ID)
• FROM SPRIDEN, SGBSTDN B
• WHERE B.SGBSTDN_STST_CODE IN
• (SELECT STVSTST_CODE FROM STVSTST
• WHERE STVSTST_REG_IND 'Y')
• AND B.SGBSTDN_TERM_CODE_EFF
• (SELECT MAX(C.SGBSTDN_TERM_CODE_EFF) FROM
  SGBSTDN C
• WHERE C.SGBSTDN_TERM_CODE_EFF lt '200710'
• AND C.SGBSTDN_PIDM B.SGBSTDN_PIDM
• AND B.SGBSTDN_LEVL_CODE IN ('UG', 'GR',
  'LA'))
• AND B.SGBSTDN_TERM_CODE_CTLG_1 gt '200610'
• AND SPRIDEN_PIDM B.SGBSTDN_PIDM
• AND SPRIDEN_CHANGE_IND IS NULL ORDER BY
  SPRIDEN_ID

19

• Advisor
• To run the advisor extract using
  banneradvisors.sql file in the LOCAL_HOME/sql
  directory
• bannerextract advisor
• You may also specify a different sql file in the
  LOCAL_HOME/sql directory
• bannerextract advisor someadvisors.sql
• You may also specify a file of advisor IDs in the
  LOCAL_HOME/sql or current directory
• bannerextract advisor someadvisor.ids
• You may also supply a single advisor ID instead
  of a file name
• bannerextract advisor 9876543

20

• ADVISOR LOCAL_HOME/sql/banneradvisors.sql
• -- Change this select stmt to be whatever you
  want to select the pool
• -- of advisors you want pulled into DegreeWorks
• select distinct spriden_id from spriden, sgradvr,
  sgbstdn b
• where spriden_change_ind is null
• and spriden_pidm sgradvr_advr_pidm
• and (b.sgbstdn_stst_code in
• (select stvstst_code from stvstst where
  stvstst_reg_ind 'Y')
• and b.sgbstdn_term_code_eff
• (select max(c.sgbstdn_term_code_eff)
• from sgbstdn c where c.sgbstdn_term_code_
  eff lt '200710'
• and c.sgbstdn_pidm b.sgbstdn_pidm)
• and b.sgbstdn_levl_code in('UG', 'GR')
• and b.sgbstdn_term_code_ctlg_1 gt
  '200410')
• and b.sgbstdn_pidm sgradvr_pidm order by
  spriden_id

21

• Staff
• To run the staff extract using bannerstaff.sql
  file in the LOCAL_HOME/sql directory
• bannerextract staff
• You may also specify a different sql file in the
  LOCAL_HOME/sql directory
• bannerextract staff somestaff.sql
• You may also specify a file of staff IDs in the
  LOCAL_HOME/sql or current directory
• bannerextract staff somestaff.ids
• You may also supply a single staff ID instead of
  a file name
• bannerextract staff 7654321

22

• STAFF This file is not delivered you may
  create your own
• LOCAL_HOME/sql/bannerstaff.sql
• Normal practice is to create a staff.ids file of
  SPRIDEN IDs (and passwords) and use it as input
• bannerextract staff staff.ids

23

• Selected UCX Tables
• To run the ucx extract using a list of
  DegreeWorks UCX tables listed in a file with a
  .ids extension in LOCAL_HOME/sql (do not use
  with cron)
• bannerextract ucx someucxtables.ids
• The LOCAL_HOME/sql/someucxtables.ids file might
  contain tables (do NOT include the UCX_)
• STU352
• STU560
• STU563
• In this case ONLY these 3 UCX tables will be
  re-extracted from Banner and loaded into the
  appropriate UCX_STU table.

24

• Deleting IDs
• Run deleteid to remove unwanted ID codes from the
  DegreeWorks database using the bannerdeleteids.sql
  file in the LOCAL_HOME/sql directory (Warning
  do not use with cron! )
• bannerextract deleteid
• You may also specify a different sql file in the
  LOCAL_HOME/sql directory
• bannerextract deleteid somedeletes.sql
• You may also specify a file of IDs in the
  LOCAL_HOME/sql or current directory
• bannerextract deleteid somedeletes.ids

25

• Client SQL Configuration File
• ADMIN_HOME/common/bannerextract.config
• Allows the data to be extracted from Banner to
  match how you are storing data in Banner
• Every table with a SELECT clause in the extract
  uses bannerextract.config
• The sequence is as follows
• RAD30 selects the pool of ID codes, Course keys,
  etc.
• The extract uses the bannerextract.config file to
  extract the data for each IDs/Keys passed by
  RAD30.

26

• Examples from bannerextract.config
• SPRIDEN - Individual (student/advisor/staff)
  
• Standard is to only select by the SPRIDEN_PIDM
  and a SPRIDEN_CHANGE_IND of NULL
  but you may add to the WHERE clause if needed
  --- but don't forget the final ANDSPRIDEN-from
  FROM SPRIDEN aSPRIDEN-where WHERE
  a.SPRIDEN_PIDM ltindividual's pidmgt AND
  a.SPRIDEN_CHANGE_IND IS NULL

27

• Examples from bannerextract.config (cont)
• SORLCUR - CURRICULUM (ban40)
  SORLCUR must be a AND is required
  at the end of WHERESORLCUR-from FROM SORLCUR
  aSORLCUR-where WHERE a.SORLCUR_CACT_CODE
  'ACTIVE'SORLCUR-where AND a.SORLCUR_SEQNO
  SORLCUR-where (SELECT MAX(b.SORLCUR_SEQNO)
  FROM SORLCUR bSORLCUR-where WHERE
  b.SORLCUR_PIDM a.SORLCUR_PIDMSORLCUR-
  where AND b.SORLCUR_PRIORITY_NO
  a.SORLCUR_PRIORITY_NOSORLCUR-where AND
  b.SORLCUR_LMOD_CODE 'LEARNER')
  SORLCUR-where AND
  a.SORLCUR_PIDM ltstudents-pidmgt

28

• Password Setup in bannerextract.config
• 3 Keywords
• PASSWORDSTU for students
• PASSWORDADV for advisors
• PASSWORDSTF for staff
• A concatentated password (shp_access_code) can be
  generated using a SQL statement using whatever
  tables the client wants to use.
• If no valid password is generated (blank) then a
  random password will be generated (using
  alphabetic and numeric characters 10-bytes)

29

• Examples from bannerextract.config (cont)
• PASSWORDSTU - WEB Password (ban40)
  
• This special SQL is used to create the piece of
  data to be loaded
• into the shp_access_code as the SHP password
  for Students.
• The SELECT clause MUST only return 1 data
  value.
• If the value is BLANK or no valid record is
  found a RANDOM
• 10-byte alphanumeric password will
  automatically be generated.
• The STU_PIDM MUST be somewhere in the WHERE
  clause.
• The STU_PIDM will be replaced with the real
  PIDM processed.

30

• Examples from bannerextract.config (cont)
• PASSWORDSTU - WEB Password (ban40)
  
• DO NOT DELETE THE ENTRIES BELOW!!! LEAVE THE
  KEYS AND
• BLANK OUT EVERYTHING AFTER THE COLON IF A
  RANDOM
• PASSWORD IS TO BE GENERATED!!!
• PASSWORDSTU-select SELECT RPAD('S'
• PASSWORDSTU-select RPAD(a.SPBPERS_DRIVER_L
  ICENSE,6)
• PASSWORDSTU-select RPAD(a.SPBPERS_ETHN_COD
  E,1)
• PASSWORDSTU-select RPAD(a.SPBPERS_BIRTH_DA
  TE,2)
• PASSWORDSTU-select RPAD(a.SPBPERS_LEGAL_NA
  ME,4),14) password_key
• PASSWORDSTU-from FROM SPBPERS a
• PASSWORDSTU-where WHERE a.SPBPERS_PIDM
  STU_PIDM
• PASSWORDSTU-orderby ORDER BY SPBPERS_ACTIVITY_DAT
  E DESC

31

• Added password field to the Staff .ids file
  in the LOCAL_HOME/sql/ directory
• Optional if password included after the Staff
  ID (starts in byte 11) it will be used as the
  shp_access_code for that staff member.
• (NOTE the PASSWORDSTF entries in
  bannerextract.config must be left blank for this
  password to be used).

32
Banner Extracts

• Configuration Decisions
• Email
• Repeat Policies
• Repeatable Policies
• Equivalencies
• Cross Listed Courses
• Skip classes (do not import to DegreeWorks)
• Class Flag Overrides (e.g. In-Progress,
  Insufficient)
• Identify Student/Advisor/Staff Population to Load
• Custom Banner Data

33

• Settings
• Change UCX-SCR001 DegreeWorks Field Names to
  Banner Field Names
• Status to Student Type
• School to Level
• Level to Student Class Level
• Set the UCX-CFG020 flags/values
• BANNER record
• REFRESH record
• SEARCH record

34
Banner Extracts

• UCX-CFG020 BANNER Configuration

35
Banner Extracts

• Repeat Policy
• SHRTCKN_REPEAT_COURSE_IND
• I always include
• E skip or include?
• A skip or include?

• LAST credits and grade points count in
  DegreeWorks. Earlier occurrences are forced to
  insufficient and do NOT count.
• BEST credits and grade points count in
  DegreeWorks. All other occurrences go to
  insufficient and do NOT count.
• All occurrences count in the credits and GPA
  calculations in DegreeWorks. Classes will be
  applied where they fit.
• TG-40

36
Banner Extracts

• Repeat Policy (continued)

1. All sets of grades and grade points count for GPA
   calculations. ONLY the credits from the LAST
   occurrence count in DegreeWorks. All other
   occurrences go to insufficient.
2. All occurrences of the repeated class will be
   listed on the DegreeWorks audit where they could
   apply (all occurrences will stay grouped together
   by DegreeWorks). All sets of grades and grade
   points are used in the GPA calculation, but only
   credits for the class with the BEST grade are
   counted. The BEST grade is also used for MINGRADE
   checks.
3. Keep THIS occurrence of the class. This repeat
   policy is used when none of the five policies
   above fit your sites requirements. All other
   occurrences should be marked with a 0 Repeat
   Policy and will go to insufficient.

37
Banner Extracts

• Repeat Policy (continued)
• Best to use Policy B for all indicators
• Excluded classes show in Insufficient but do not
  affect the GPA
• Averaged classes show in Insufficient but do
  affect the GPA
• Included classes apply to rules as normal
  classes.
• Repeatable Courses
• Courses that can be taken several times for
  credit (e.g. Music Lessons, PE activity classes,
  etc.)
• Repeatable Option defines how to correctly
  extract courses that are truly repeatable (i.e.
  not repeated for a better score)
• Documentation
• Banner Registration
• DGW Technical Guide Banner Data Mapping for BIF
  Class Repeats / Multiple Occurrences section
• DGW Technical Guide UCX

38
Banner Extracts

• Repeatable Courses
• N DO NOT check the SCBCRSE_REPEAT_LIMIT or
  SCBCRSE_MAX_RPT_UNITS
• L Check the SCBCRSE_REPEAT_LIMIT only (Default
  if Repeatable Option BLANK)
• U Check the SCBCRSE_MAX_RPT_UNITS only
• B Check Both the SCBCRSE_REPEAT_LIMIT and
  SCBCRSE_MAX_RPT_UNITS
• I Include the class credits in the
  SCBCRSE_REPEAT_LIMIT and SCBCRSE_MAX_RPT_UNITS
  before checking for repeatable classes using the
  SCBCRSE_CREDIT_HR_LOW

39

• Settings
• Load ATTRIBUTE into UCX-SCR002 FOR Student
  Attributes. These values are available for use
  with the WITH keyword in Scribe.
• If (Attribute HONR) then
• 15 Credits in ENGL 4_at_
• Label 15 upper-division credits required
• Load ATTRIBUTE into UCX-SCR044 FOR Class
  Attributes. These values are available for use
  with the WITH keyword in Scribe.
• 5 Credits in ENGL _at_ (With Attribute HONR)

40
Banner Extracts

• Equivalencies
• Course that has changed Discipline and/or Number
  within an institution
• Extract equivalencies from SCREQIV only?
• Equivalency DegreeWorks will apply MATH 102 to
  the Math 110 requirement for students who took
  MATH 102 in 200610
• Catalog year course taken Student catalog year
  new course
• 200610 MATH 102 _at_ MATH 110

41

• Equivalences
• Two scribing options
• 1 Class in MATH 110 was 102
• 1 Class in MATH 110 Formerly 102
• Banner Extract EQUIV loads UCX-CFG073
• 1. Launch Banner Extract and Bridge (RAD30) for
  the EQUIV mode.
• 2. Set the UCX-CFG020 DAP13 Process Equivalences
  Y.
• 3. Launch Parse Blocks (DAP16)
• Your requirements now use the new course number

42
Banner Extracts

• Cross-listed Courses
• Courses that are the same course offering but
  with different course numbers
• Two scribing options
• 1 Class in MATH 101, PHIL 101, STAT 101
• 1 Class in MATH 101 Hide PHIL 101, STAT 101

43
Banner Extracts

• Cross-listed Courses

UCX-CFG073 MATH 101 cross-listed with PHIL
101 MATH 101 cross-listed with STAT
101 MATH 102 cross-listed with PHIL 102
Scribe BEGIN MaxCredits 9 in MATH 101,
102 5 Credits in MATH 101 , 102 Label "My
rule 1" END.
44
Banner Extracts

• Cross-listed Courses

• Banner Extract EQUIV loads UCX-CFG073
• 1. Set the CFG020 BANNER Cross List in SCREQIV
  Y.
• 2. Launch Banner Extract and Bridge (RAD30) for
  the EQUIV mode.
• 3. Set the CFG020 DAP13 Process Cross-Listings
  Y.
• 4. Launch Parse Blocks (DAP16)
• Your requirements will now be cross-listed aware

45
Banner Extracts

• Skip Classes
• By SCHD_CODE (UCX-BAN001 and UCX-BAN002)
• By GMOD_CODE (UCX-BAN003)
• By Subject Code (Discipline) (UCX-STU352)
• Or simply change bannerextract.config

46
Banner Extracts

• Override DegreeWorks Class Flags (UCX-STU385)
• By Banner Level, Grade Type (Mode) and Grade
• Audit Flag Incomplete Flag
• Insufficient Flag Passed Flag
• In Progress Flag Pass/Fail Flag
• Withdraw Flag Final Grade Number
• Only set the flags you want to override
• Leave a flag blank if you want to use the Banner
  value

47
Banner Extracts

• Override Configuration (UCX-STU385)

48

• Why Use UCX-BAN080?
• Allows you to extract custom pieces of data
  into DegreeWorks that are not extracted by the
  standard student extract program.
• 3 Types of Records
• AID used in Financial Aid audits
• Data is loaded into the rad_aid_dtl
• CUSTOM used in IF statements on audits
• Data is loaded into the rad_custom_dtl
• REPORT used on audit headers
• Data is loaded into the rad_report_dtl

49
Banner Extracts

• Extracting additional Banner data
• Extract values from any table by PIDM
• Stored by use
• Custom data stored in rad-custom-dtl
• Report data stored in rad-report-dtl
• SureCode entry
• Dynamic SQL Definitions (UCX-BAN080)
• Custom Data (UCX-SCR002)

50
Banner Extracts

• Dynamic SQL Definitions (UCX-BAN080)

51
Banner Extracts

• Custom Data (UCX-SCR002)

52
Banner Extracts

• Command Line
• Extract modes
• Advisor Course
• Equiv Staff
• Student UCX
• Run - bannerextract mode
• SQL used to select students/advisors/staff
• Run bannerextract extractname scriptname.sql
• File of IDs bannerextract extractname idfile.ids
• Schedule using your preferred job scheduler (e.g.
  cron, at)

53

• Summary of SQL Used
• LOCAL_HOME/sql/banner
• Used to SELECT the pool of IDs for Banner
  Extracts
• ADMIN_HOME/common/bannerextract.config
• Used to EXTRACT data from Banner for the pool of
  IDs
• UCX-CFG080
• Used to extract CUSTOM pieces of data from Banner
  for the audits (IF rules, Audit headers, AID
  audits)
• If Banner tables not used by DegreeWorks are used
  in ANY of the above SQL files make sure to get
  access granted to the DegreeWorks users by the
  clients DBA

54

• Documentation Related to the Banner Extract
• DGW_Technical_Guide_Banner_Considerations
• DGW_Technical_Guide_Banner_Data_Mapping_for_BIF
• DGW_Technical_Guide_UCX
• BAN001, BAN002, BAN003, BAN080
• CFG020 BANNER, REFRESH, SEARCH
• EQUIV Tables - CFG070, CFG073, CFG074, CFG078