Best Practices A People Soft DataTable Reference for

Best Practices: A People. Soft Data/Table Reference for FA Users Session 3133 October 8, 2015 1: 00 PM 2015 MIDHEUG Alliance

Your Presenters Karen Kilps IS Developer at University of Wisconsin. Milwaukee 26 years as developer/15 years People. Soft Neelima Tummala Assistant Director – Systems and Data Financial Aid – Marquette University 11 years working with FA and People. Soft 2015 MIDHEUG Alliance

Overview This session will be a quick overview of most frequently used FA tables, relationships to other key tables, what data one might find there, are these table currently pop-updatable, etc. Also a discussion of other available resources, on HEUG, People. Books and otherwise, when trying to figure out what table to use to answer a question/find a population. 2015 MIDHEUG Alliance

Agenda Learning Objectives: 1. Identify key tables used in the FA world. 2. Identify relationships between some key tables. 3. Give samples of how data could be used and pop updated if available. 4. Identify available resources to help us in our quest of FA data. 2015 MIDHEUG Alliance

University of Wisconsin Milwaukee Public University 28, 000+ students Live in 2001 with People. Soft Campus Solutions. Currently at 9. 0, People. Tools version 8. 53. 16 2015 MIDHEUG Alliance

Marquette University Live in 2004 with People. Soft Campus Solutions. Currently at 9. 0 (since 2008), People. Tools version 8. 54. 10 2015 MIDHEUG Alliance

Contents Resources to find table/field info Financial aid Aid year activation & friends ISIR processing FA term Budgets Awards and disbursement FA item type setup Loan and pell origination SAP SULA Bio Demo Security Equations Admissions Student records Student financials 2015 MIDHEUG Alliance

Resources to Find table/field Info Page references using browser (inspect element) App Designer Peoplebooks/Bundle Documentation HEUG listserv Sample Delivered queries (QA_CS_FA and searched) 2015 MIDHEUG Alliance

Resources to Find table/field Info Page references using browser (inspect element) Using browser Firefox or Chrome (not Internet Explorer) Right click over field and select Inspect Element 2015 MIDHEUG Alliance

Resources to Find table/field Info Page references using browser (inspect element) Lists table name and field (when done, X on bottom right to close inspector window) 2015 MIDHEUG Alliance

Resources to Find table/field Info App Designer Hit Ctrl-J on keyboard and box appears giving page information 2015 MIDHEUG Alliance

Resources to Find table/field Info App Designer 2015 MIDHEUG Alliance

Resources to Find table/field Info Peoplebooks/Bundle Documentation 2015 MIDHEUG Alliance

Resources to Find table/field Info People. Books/Bundle Documentation 2015 MIDHEUG Alliance

Resources to Find table/field Info People. Books/Bundle Documentation 2015 MIDHEUG Alliance

Resources to Find table/field Info HEUG Listserv Type in search box, retrieve related info 2015 MIDHEUG Alliance

Resources to Find table/field Info Sample Delivered queries (QA_CS_FA) 2015 MIDHEUG Alliance

Resources to Find table/field Info Sample Delivered queries (search criteria) 2015 MIDHEUG Alliance

Resources to Find table/field Info Sample Delivered queries (Retrieves all queries with search criteria) 2015 MIDHEUG Alliance

FINANCIAL AID 2015 MIDHEUG Alliance

AID YEAR ACTIVATION & FRIENDS 2015 MIDHEUG Alliance

STUDENT_AID Main table for the Financial Aid system. A student applying for aid would have a new record each year, and this record contains high level information relating to the application, awarding and processing of financial aid for the year. Find all students who are aid year activated for a given year as a starting point for a query. 2015 MIDHEUG Alliance

STDNT_AID_ATRBT One more year per career for aid year activated student. High level information relating to the application, awarding and processing of financial aid for the year. Self Service Access Pop updatable. Useful reporting: packaging, verification, review, and application status Find all students who are not packaged with review status as incomplete. 2015 MIDHEUG Alliance

STDNT_AID_ATRBT Pop update fields: 2015 MIDHEUG Alliance

STDNT_AWD_PER Child table to STUDENT_AID. Contains COA, EFC, and need data for the academic and/or non standard award periods. Also – unmet need and over-award amounts. Find students with official EFCs for year as starting point for query. Find students with current unmet need. Find students who are federally over-awarded. 2015 MIDHEUG Alliance

STDNT_PKG_VAR Various formatted fields to be user defined. Can be used in packaging equations or queries in pop selection. Key data is by student and aid year. Find all students who have used more than 8 semesters of a scholarship, where variable number 7 = scholarship terms used. 2015 MIDHEUG Alliance

STDNT_PKG_VAR Pop update fields: 2015 MIDHEUG Alliance

ISIR PROCESSING 2015 MIDHEUG Alliance

ISIR_CONTROL, FAN_ISIR_CTL_VW “Header” information about a student’s ISIR: - Transaction number - Dates - EFC Status - Correction Status (pop updatable field) Does the student have an official EFC as of today? As of which transaction did Pell pay? 2015 MIDHEUG Alliance

ISIR_CONTROL, FAN_ISIR_CTL_VW Dependency override PJ Pell ID Pushed ISIR CPS processing info Parent IRS DRT info Student IRS DRT info How many dependency overrides? PJ statistics? Pell ID? Transaction # mismatch? Find students for whom to waive checklists if parent/student IRS DRT = 02? 2015 MIDHEUG Alliance

ISIR_CONTROL, FAN_ISIR_CTL_VW Database matches/flags Correction source (school/applicant/CPS) ISIR citizenship vs school records? Title IV eligible? Pell eligible? Needs verification? Verification group? Is the most recent transaction a result of a change we (school) made? 2015 MIDHEUG Alliance

ISIR_PARENT, FAN_ISIR_PAR_VW Parent marital status & date Parent legal residence Parent 1 and Parent 2 identifiers Parent email address Number in family and college List of all parent email addresses for FAFSA related communication. Find siblings based on matching parent identifiers in case of conflicting information. 2015 MIDHEUG Alliance

ISIR_PARENT, FAN_ISIR_PAR_VW Federal benefits Parent tax filing information Parent financial information Find parents who indicated they are dislocated workers for follow up. 2015 MIDHEUG Alliance

ISIR_STUDENT, FAN_ISIR_STD_VW Student Bio/Demo information from ISIR – name, DOB, SSN. Student ISIR email address. List of all student email addresses for FAFSA related communication, in addition to campus email. 2015 MIDHEUG Alliance

ISIR_STUDENT, FAN_ISIR_STD_VW Student state of residence Male/Female High school information Rec’d first bachelor’s degree already? Work-study interest? Drug conviction? Find award eligible students who haven’t already earned bachelor’s degree. Find students who meet state award’s residency requirements. 2015 MIDHEUG Alliance

ISIR_STUDENT, FAN_ISIR_STD_VW Federal benefits Student tax filing information Student financial information Find students who didn’t report at least as much FWS as you know they earned as need based employment earnings. 2015 MIDHEUG Alliance

ISIR_STUDENT, FAN_ISIR_STD_VW Student dependency status and supporting information. Number in family and college for independent students. Find all students who meet specific dependency information criteria for awards/scholarships. 2015 MIDHEUG Alliance

ISIR_STUDENT, FAN_ISIR_STD_VW Student dependency status and supporting information. Number in family and college for independent students. Find all students who meet specific dependency information criteria for awards/scholarships. 2015 MIDHEUG Alliance

ISIR_COMMENTS ISIR comment codes for student for aid year Find all students who have been selected for fed verification based on comment code 170/171 2015 MIDHEUG Alliance

ISIR_COMPUTED, FAN_ISIR_CMP_VW EFC Formula Auto Zero Parental Contribution and other calculated fields Find siblings with mismatched PCs to investigate why different. 2015 MIDHEUG Alliance

ISIR_00_1_EC, ISIR_00_2_EC Details about ISIRs when in suspense: Load status (1) Transaction rec’d date (1) Student identifiers (1) Dependency Status (2) Reject reasons (2) Find ISIRs in suspense with rejects. 2015 MIDHEUG Alliance

FA TERM 2015 MIDHEUG Alliance

STDNT_FA_TERM, FAN_FA_TERM_VW The core of Financial Aid processing, effective dated data enables you to project or maintain actual data related to the student to determine eligibility of financial aid. Includes career, plans, enrollment, and financial aid standing information. All subsequent processing depends on FA Term data. Useful to track, compare, and provide historical information about the student. Find all students who have active FA Terms for a term, as a starting point for a query. 2015 MIDHEUG Alliance

STDNT_FA_TERM FA Term pop update fields: Tip: pop updatable fields can be found in table ps_scc_pop_upd_set 2015 MIDHEUG Alliance

STDNT_FA_TERM 2015 MIDHEUG Alliance

FA_FATERM_AUDIT Audit table for FA Term. Table Navigation: Financial Aid, Financial Aid Term, Maintain Student FA Term, Financial Aid Info, Field Audits link. Can be used to find changes that have occurred in FA TERM for the student. People Books 9. 0 2015 MIDHEUG Alliance

FA_FATERM_AUDIT 2015 MIDHEUG Alliance

BUDGETS 2015 MIDHEUG Alliance

STDNT_TERM_BDGT Effective dated budget groups for a student, by term. Budget group Institutional and Fed COA Pell COA Find students who were budgeted in a group that needs to be reviewed. Find students who may have Pell COA doubled because they were going to be one semester, but then continued on for the full year. 2015 MIDHEUG Alliance

STDNT_BUDGET_IT For all budget items for a student budget for a term: Budget Amount Pell LHT Amount Find all students who have a instrument budget item in their budget that needs to be refreshed due to a change in instrument costs. 2015 MIDHEUG Alliance

SFASTDTRMBDGTVW A view that uses stdnt_term_bdgt max effective dated row. Has all term related budget information such as budget_group_code, acad_prog and acad_plan, residency, housing, more. Useful in packaging equations in this example, finds residency 2015 MIDHEUG Alliance

AWARDS & DISBURSEMENT 2015 MIDHEUG Alliance

STDNT_AWARDS Award information for student by aid year. Offer, accept, authorized and disbursed amounts for the aid year. Related to stdnt_awd_pkg table used on the “Assign awards to a Student” page and includes award, lock, EA indicator, and PJ/override information. NOT term based. Awards by award type, award amounts (offered through disb), locked, award status 2015 MIDHEUG Alliance

STDNT_AWARDS Pop update fields: 2015 MIDHEUG Alliance

STDNT_AWRD_ACTV All activity for an award-what happened when and who did it. What did a student’s award look like on a certain date/time – use action_dttm in this table to find most ‘current’ row as of the given date/time. Note award_disb actions include: O - Offer, B - Offer/Accept, H - Authorize Disb, P - Disburse C - Cancel 2015 MIDHEUG Alliance

STDNT_AWRD_DISB Detail disbursement information with disbursement plan/splits/ids. Keys emplid, institution, aid_year, item_type. Also strm (not a key) Includes aggregate information. How much money was disbursed for Pell item types for the fall term? Note: when “aggregate” errors occur in packaging, check the aggregate level in this table 2015 MIDHEUG Alliance

STDNT_DISB_VW 1 Useful view that joins stdnt_awards, stdnt_awrd_disb, and disb_id_tbl to get proper term award and disbursement information with disbursement plan/splits/ids. Keys emplid, institution, aid_year, strm, item_type, disbursement_id. 2015 MIDHEUG Alliance

AGGREGATE TABLES STDNT_AGGR_AY: has aggregate totals for each student by area, aid_year, and level STDNT_AGGR_LIFE: has aggregate totals for each student for grad and undergrad, nslds_aggr_amount, nslds push dates, percentage used, and lifetime eligibility used. STDNT_AGGR_SCHL: aggregate totals by school code, mainly to keep track of incoming aggregate data. 2015 MIDHEUG Alliance

STDNT_FA_MSGS (USER EDITS) User maintained table used for storing and maintaining user edit messages regarding Financial Aid processing. Generally used for common problems that need review. Find all students who have unresolved user edits that will prevent disbursement. 2015 MIDHEUG Alliance

STDNT_FA_MSGS Can be added, updated, deleted in batch. 2015 MIDHEUG Alliance

FA ITEM TYPE SETUP 2015 MIDHEUG Alliance

ITEM_TYPE_FA FA item type descr Fin Aid type Source Federal ID Aggregate area Awarding rules Disbursement method Award message Self Service actions Find all students with Perkins awards (using federal ID PERK). Find all item types that are included in an aggregate area. 2015 MIDHEUG Alliance

ITEM_TP_TERM_LMT, ITEM_TP_FA_DISB, ITEM_TYP_FA_FEE Term category, min, max for item type Default disbursement plan and split code by career, institution. Loan program, fees for item type. Find all item types setup with the pre-10 -01 disbursement plan as default. Find students who have loans that need to be swapped out post 10 -01 due to loan fee change. 2015 MIDHEUG Alliance

ITEM_TYPE_FISCL Fiscal totals of item types by aid year. Includes amounts offered, accepted, and disbursed. Also a total count of each field. Useful in monitoring use of awards and remaining amounts. 2015 MIDHEUG Alliance

DISB_RULE_ITM Controls which delivered rules are used for disbursement, by item type. 2015 MIDHEUG Alliance

MORE ITEM TYPE DISB RULES Disb_rule_chk – Disb Rule Checklist/Tracking page, checklist control Disb_rule_itk – Disb IT Rule Checklist/Tracking page, Tracking control Disb_rule_its – Disb IT Rule Checklist/Tracking page, service impact control Disb_rule_itu – Disb IT Rule Checklist/Tracking page, user edits messages control 2015 MIDHEUG Alliance

MORE ITEM TYPE DISB RULES 2015 MIDHEUG Alliance

LOAN TYPE SETUP 2015 MIDHEUG Alliance

LN_TYPE_TBL Loan types, descr Loan category, program Loan fee rate What are all the loan types that have the pre-10 -01 fee rate? How many loan types did we setup with the new fee rate? What item types have been setup as ‘Additional Unsub’? 2015 MIDHEUG Alliance

LN_ITEM_TBL Item types tied to a loan type What are all the item types that have the pre-1001 fee rate? Did we tie the post -10 -01 item types to the post-10 -01 loan types? 2015 MIDHEUG Alliance

LOAN & PELL ORIGINATION 2015 MIDHEUG Alliance

LOAN_ORIGNATN, LOAN_ORIG_DTL Pop update fields: Origination detail, loan processing status, where HOLD is updated. 2015 MIDHEUG Alliance

LOAN_DISBMNT Pop update fields: Cod status, tracks disbursement, contains SULA info, disbursement transaction status, payment start/end dates Also note: LOAN_DISB_ACTN – school and COD exchange status 2015 MIDHEUG Alliance

PELL_ORIGNATN, PELL_DISBMNT, PELL_ORIG_DTL Pop update fields: 2015 MIDHEUG Alliance

SAP 2015 MIDHEUG Alliance

SFA_SAP_STDNT 2015 MIDHEUG Alliance

SFA_SAP_RPT Simulation run of Sap process. Could be used to compare results from before and after. 2015 MIDHEUG Alliance

SAP: Compare of Sim reports Use query to look at run made from prompted date. For example, you rerun sap in simulation after a setup change to verify results. SELECT A. EMPLID, A. ACAD_CAREER, A. STRM, TO_CHAR(CAST((A. PROCESS_DTTM) AS TIMESTAMP), 'YYYYMM-DD-HH 24. MI. SS. FF'), A. ACAD_PROG, A. ACAD_PLAN, A. SFA_SAP_STATUS, A. SFA_SAP_STAT_CALC, A. SFA_PROCESS_OPRID, A. SFA_SAP_PROCMSG, A. SFA_SAP_COMMENTS FROM PS_SFA_SAP_RPT A WHERE ( A. OPRID = 'KILPSK' AND ( A. PROCESS_DTTM > TO_DATE(: 1, 'YYYY-MM-DD') ) ) 2015 MIDHEUG Alliance

SAP: Review students near max credits allowed SELECT A. EMPLID, D. NAME, A. ACAD_CAREER, A. STRM, A. ACAD_PROG, A. ACAD_PLAN, A. STDNT_CAR_NBR, A. SFA_CUM_ATT_UNITS, A. SFA_CUM_ERN_UNITS, C. SFA_SAP_MAX_ATTFRM, A. SFA_SAP_STAT_CALC, A. SFA_SAP_STATUS FROM PS_SFA_SAP_STDNT A, PS_SFA_SAP_MAX_ATT C, PS_NAMES D WHERE ( A. PROCESS_DTTM = (SELECT MAX( B. PROCESS_DTTM) SAP setup table FROM PS_SFA_SAP_STDNT B WHERE A. EMPLID = B. EMPLID for Max attempted AND A. INSTITUTION = B. INSTITUTION units test AND A. ACAD_CAREER = B. ACAD_CAREER AND A. STRM = B. STRM) AND A. STRM = : 1 AND A. ACAD_CAREER = : 2 AND A. INSTITUTION = C. INSTITUTION AND A. ACAD_CAREER = C. ACAD_CAREER AND C. ACAD_PROG = A. ACAD_PROG AND C. ACAD_PLAN = A. ACAD_PLAN AND C. EFFDT = (SELECT MAX(C_ED. EFFDT) FROM PS_SFA_SAP_MAX_ATT C_ED Prompt for number WHERE C. INSTITUTION = C_ED. INSTITUTION of credits away AND C. ACAD_CAREER = C_ED. ACAD_CAREER AND C_ED. EFFDT <= SYSDATE) from failing max AND A. SFA_CUM_ATT_UNITS > C. SFA_SAP_MAX_ATTFRM - : 3 credit amount AND A. EMPLID = D. EMPLID AND D. EFFDT = (SELECT MAX(D_ED. EFFDT) FROM PS_NAMES D_ED WHERE D. EMPLID = D_ED. EMPLID AND D. NAME_TYPE = D_ED. NAME_TYPE AND D_ED. EFFDT <= SYSDATE) AND D. NAME_TYPE = 'PRI' ) 2015 MIDHEUG Alliance

SAP: Review students near max credits allowed 2015 MIDHEUG Alliance

SULA 2015 MIDHEUG Alliance

SFA_CRDLVL_XREF 2015 MIDHEUG Alliance

SFA_CRDLVL_XREF Queries could be used against this table to compare Records vs. Financial Aid setup, as well as student data 2015 MIDHEUG Alliance

SFA_CRDLVL_XREF SELECT DISTINCT A. ACAD_CAREER, A. ACAD_PLAN, A. ACAD_PROG_PRIMARY, B. SSR_NSC_CRD_LVL, D. XLATLONGNAME, C. SSR_NSC_CRD_LVL, C. SFA_COD_CRED_LVL, B. ACAD_PROG, C. ACAD_PROG, B. ACAD_CAREER, B. SSR_PROG_LEN_TYPE, B. SSR_PROG_LENGTH, B. SFA_SPEC_PROG_FLG, E. FA_ELIGIBILITY FROM PS_STDNT_FA_TERM A, PS_ACAD_PLAN_TBL B, PS_SFA_CRDLVL_XREF C, XLATTABLE_VW D, PS_ACAD_PROG_TBL E WHERE ( A. INSTITUTION = 'UWMIL' AND A. AID_YEAR = : 1 AND A. STRM = : 2 AND A. EFFDT = (SELECT MAX(A_ED. EFFDT) FROM PS_STDNT_FA_TERM A_ED WHERE A. EMPLID = A_ED. EMPLID AND A. INSTITUTION = A_ED. INSTITUTION AND A. STRM = A_ED. STRM AND A_ED. EFFDT <= SYSDATE) AND A. EFFSEQ = (SELECT MAX(A_ES. EFFSEQ) FROM PS_STDNT_FA_TERM A_ES WHERE A. EMPLID = A_ES. EMPLID AND A. INSTITUTION = A_ES. INSTITUTION AND D. EFFDT = AND A. STRM = A_ES. STRM (SELECT MAX(D_ED. EFFDT) FROM XLATTABLE_VW D_ED AND A. EFFDT = A_ES. EFFDT) WHERE D. FIELDNAME = D_ED. FIELDNAME AND A. INSTITUTION = B. INSTITUTION AND D. FIELDVALUE = D_ED. FIELDVALUE AND B. ACAD_PLAN = A. ACAD_PLAN AND D_ED. EFFDT <= SYSDATE) AND B. EFFDT = AND D. FIELDNAME = 'SSR_NSC_CRD_LVL' (SELECT MAX(B_ED. EFFDT) FROM PS_ACAD_PLAN_TBL B_ED AND D. FIELDVALUE = B. SSR_NSC_CRD_LVL WHERE B. INSTITUTION = B_ED. INSTITUTION AND A. INSTITUTION = E. INSTITUTION AND B. ACAD_PLAN = B_ED. ACAD_PLAN AND E. EFFDT = AND B_ED. EFFDT <= SYSDATE) (SELECT MAX(E_ED. EFFDT) FROM PS_ACAD_PROG_TBL AND A. INSTITUTION = C. INSTITUTION E_ED AND C. AID_YEAR = A. AID_YEAR WHERE E. INSTITUTION = E_ED. INSTITUTION AND C. ACAD_CAREER = A. ACAD_CAREER AND E. ACAD_PROG = E_ED. ACAD_PROG AND C. ACAD_PLAN = A. ACAD_PLAN AND E_ED. EFFDT <= SYSDATE) AND B. SSR_NSC_CRD_LVL <> C. SSR_NSC_CRD_LVL AND E. ACAD_PROG = A. ACAD_PROG_PRIMARY ) 2015 MIDHEUG Alliance ORDER BY 2, 3

BIO DEMO 2015 MIDHEUG Alliance

ADD/ UPDATE A PERSON Tables behind this page have: Names Addresses SSN Email Addresses DOB Marital Status Gender Citizenship Data Visa Data 2015 MIDHEUG Alliance

PERSON, SCC_PERDATA_QVW DOB Birth details: country, state. Check before sending emails/ letters 2015 MIDHEUG Alliance

PERS_DATA_EFFDT, SCC_PERDATA_QVW Gender? Marital Status? Effective dates? Find students who need selective service checklists waived because they are female. 2015 MIDHEUG Alliance

NAMES, SCC_NAMES_QVW Name by type? When added? When changed? What changed? (History) Missing critical name type? Add name to students who are set to Review Status = Required so list can be sorted and distributed. Type specific name views on your campus? MU has a effective dated primary name view, may save your effective dating steps. 2015 MIDHEUG Alliance

ADDRESSES Address by type? When added? When changed? Missing critical address type? List Billing/Home address of all students who need exit counseling. Type specific address views on your campus? 2015 MIDHEUG Alliance

EMAIL_ADDRESSES, SCC_EMAIL_QVW Email address by type? Preferred email? Missing preferred email? Type specific email address views on your campus? List of email addresses for all potential scholarship recipients to follow up for additional information about eligibility. 2015 MIDHEUG Alliance

PERS_NID, SCC_PERS_NI_QVW SSN? Matches FAFSA? Two students with same SSN, which one has aid & other FA information tied to it? 2015 MIDHEUG Alliance

DIVERS_ETHNIC, SCC_DIV_ETH_QVW Ethnicity? Primary? When updated? Aid, debt breakdown by Ethnicity data 2015 MIDHEUG Alliance

VISA_PMT_DATA Ethnicity? Primary? When updated? Aid breakdown by Visa Permit type 2015 MIDHEUG Alliance

3 Cs 2015 MIDHEUG Alliance

COMMUNICATION Search for Communications by: - Admin Function - Comm Category - Context - Letter Code - Complete/Incomplete communications - When completed Find all students who had a recent award adjustment but have not been sent a communication about it. Find students with unsent SAP communications, to look into why they didn’t get sent out by COMMGEN.

PERSON_CHECKLST, PERSON_CHK_ITEM Search for Checklists/ Checklist items by: - Admin Function - Checklist code - Checklist status - Checklist item code - Checklist item status - Who completed? Find all Pell eligible students who still have initialized checklists so they can be sent additional follow up communications.

PERSON_COMMENT Search for Comments by: - Admin Function - Comment Code - Who entered comment - When - String within comment Find all students who have a ‘Review complete’ comment but still have Review Status set to ‘Required’ instead of complete. 2015 MIDHEUG Alliance

VAR_DATA_FINA, VAR_DATA_FINT, VAR_DATA_ISIR Variable Data (aid year or term) attached to any 3 C object. Search for any 3 C for a given year or term. 2015 MIDHEUG Alliance

SRVC_IND_DATA Service Indicators: - Positive/Negative - Start/End Term - Start/End Date Also note: Audit Table AUDIT_SRVC_IND (if you’re looking for a SI that has been released) Find all students who have a service indicator that will prevent disbursement. Find students who had their SAP service indicator recently released.

SECURITY 2015 MIDHEUG Alliance

PSOPRDEFN oprid tied to a emplid Descr for oprid (can search by part of name) Last sign on date time. Is a student’s last login time before or after the student’s award was updated? Find oprids for students with specific awards, for testing. 2015 MIDHEUG Alliance

PSROLEUSER Roles tied to a user Find a role that is common to two users in the office. 2015 MIDHEUG Alliance

PSROLECLASS Permissions lists tied to a role Combine with PSROLEUSER to find all users who have a permission list tied to them. 2015 MIDHEUG Alliance

PSAUTHITEM Page access tied to a permission list Update access? Display only? Combine PSAUTHITEM with PSROLECLAS S to find roles you could give a user to get access to a page. 2015 MIDHEUG Alliance

Query Security Find all users who have query tree access to all records in a query. SCRTY_ACC_GRP: Query trees tied to a permission list SCRTY_QUERY: Query access tied to a permission list

EQUATIONS 2015 MIDHEUG Alliance

EQUATION_TBL Listing of all equations Compile Status? Descriptions Find all equations that follow a year specific naming convention – to make a list of equations to update for the new year. 2015 MIDHEUG Alliance

EQUATION_DTL All lines in equation Records used in equation (RECNAME) Fields used in equation (FIELDNAME) Comments (notes you’ve left to yourself, searchable) Local, global variables used by equation (EQTN_OPERAND_LOC, EQTN_OPERAND_GLB) SQLs called by equation (EQTN_OPERAND_SQL) Find all equations in which a hard-coded award deadline needs to be updated.

EQTN_SQL_TBL Callable SQL for equations Do you have any callable SQLs that are affected by changes to a record in the most recent bundle? 2015 MIDHEUG Alliance

Equation Security Tables EQTN_ID_NAMAUTH - Access to Equation (by name) EQTN_TB_SQLAUTH - Access to Records EQTN_SQ_SQLAUTH - Access to Callable SQL objects 2015 MIDHEUG Alliance

ADMISSIONS 2015 MIDHEUG Alliance

ADM_APPL_PROG Program specific Admissions data: Current status? -Applicant -Admitted -Application withdrawn, etc. When admitted? Acad Program? Plan? Find students who have recently withdrawn their application – to cancel their financial aid. 2015 MIDHEUG Alliance

ADM_APPL_DATA All sorts of Admissions data, of special interest to FA: - Admit type? - Last school attended? - Housing interest? - FA interest? Find transfer students (admit type) who have been recently admitted.

STUDENT RECORDS 2015 MIDHEUG Alliance

STDNT_CAR_TERM Shows Term Activation and Term History Data Attempted/Completed units by term? For GPA/not for GPA? Cumulative attempted, completed units? Current/Cumulative GPA? Term withdrawals? LDA for term? Academic Load? Find students who have term withdrawals – exit counseling. Did the student’s term GPA match their SAP academic plan?

STDNT_ENRL Shows Enrollment Data Enrolled? How many units? Graded? Grade dates? Repeat course? Audit course? Grading basis date vs Enrollment add date? Find students who have all grades of ‘W’ type – for exit counseling, R 2 T 4. Did the student get any failing grades in a term for which they are on an academic plan for SAP?

ACAD_PROG, ACAD_PLAN, ACAD_SUBPLAN Shows Program/Plan stack information Current program, plan, sub plan? Recent change? When changed? Current plan status? (MATR/DISC/COMP) Action Date vs Effective Date? Back dated/future dated? Send exit counseling to all recently discontinued students. Find students who have been recently readmitted.

ACAD_STDNG_ACTN Shows a student’s academic standing for a given term Current acad standing. Reinstated from ‘bad’ standing. When last calculated. SAP: Auto fail students with negative academic standing? 2015 MIDHEUG Alliance

ACAD_DEGR Shows all the degrees a student has earned. Degree awarded? Completion Term? Posting Date? Back/Future dated? Also note: ACAD_DEGR_PLAN ACAD_DEGR_SPLN Exit counseling: Find students who recently had a degree posted. 2015 MIDHEUG Alliance

STDNT_GRPS_HIST Shows all the student groups of which a student was/is a member. Student is/was part of group? Group effdts back/future dated? SAP: Is student in any student group for exceptions? Budget: Check budgets for students in group, need special instruments, etc. ? 2015 MIDHEUG Alliance

TERM_TBL, SESSION_TBL Term definitions by academic career, session definitions by term and career. Term begin and end? Session begin and end? Sixty Percent date? Sessions/Term? First and last date to enroll? Dates in a term, session, check loan dates setup. Term/Session calendar for R 2 T 4.

CRSE_CATALOG Shows course data that is printed in the academic institution's course catalog. Repeatable? FA units? Letter grade or S/U? SAP: What is the default grading basis for a class a student is enrolled in? SAP: Is this class part of a series, for example ‘ 9000’ series? 2015 MIDHEUG Alliance

CLASS_TBL Shows course data by term as shown in the Schedule of Classes. Term specific class information: Subject Catalog Number Start and End dates? Instructor? Mode of Instruction? R 2 T 4: Who should be contacted to verify a student’s grade? R 2 T 4: When do classes the student is enrolled in end? 2015 MIDHEUG Alliance

GRADE_TBL Contains the valid values for Grades for each grading basis. New grades added? Special type of grade? (e. g. Audit) Included in GPA calc? SAP: Is a student in Audit only classes? SAP: Is a student’s enrollment going to affect GPA calc? 2015 MIDHEUG Alliance

STUDENT FINANCIALS 2015 MIDHEUG Alliance

ITEM_TYPE_TBL Source of item type data: - Keywords - Charge priority - Refundable? Aid breakdown by keywords. Which of a student’s awards can potentially refund? 2015 MIDHEUG Alliance

GL_INTERFACE Debit and credit General Ledger accounts tied to an item type. Find all awards made to a student from a Chemistry department GL account.

ITEM_SF, ITEM_LINE_SF, ITEM_SF_VW Charges and payments by term. Can be used for reconciliation between SF and FA.

QUESTIONS? ? 2015 MIDHEUG Alliance

CONTACTS Karen Kilps Information Systems Development Specialist University Information Technology Services University of Wisconsin - Milwaukee E-mail: kilpsk@uwm. edu Neelima Tummala Assistant Director – Systems and Data Office of Student Financial Aid Marquette University E-mail: neelima. tummala@marquette. edu 2015 MIDHEUG Alliance

This presentation and all MIDHEUG presentations are available for download from the Conference site at https: //www. heug. org/p/cm/ld/fi d=648 Note: Sessions from previous MIDHEUG and HEUG conferences are also available. 2015 MIDHEUG Alliance