Geeks on Campus

Now that Fall grades are posted, we are updating our Pell 2 offers.  However this often causes the Pell 1 offer to change when the student was not full time in Summer (our header term).  So I have used a Pop Update process to mark all of the Pell 1 awards (for students with Pell 2) as locked.  Here is a brief outline of the steps needed to do a Population Update.

1. You need for your User ID to have access to update the record.  “Set Up SACR > Security > Secure Student Administration > User ID > Population Update Security”
2. The field to be updated must be associated with the record.  “Set Up SACR > System Administration > Utilities > Population Update > Population Update Setup”
3. Write the query to select the population to update.  Don’t forget to join in the correct bind record, they are different for each record.  For the STDNT_AWARDS record the bind record is SCCPU_STAWD_BND.
4. Set up the Pop Update run control. “Set Up SACR > System Administration > Utilities > Population Update > Population Update Process”
   • If you cannot find your query here than you probably do not have the correct bind record on your query.
5. Run the process.

Here are all the records and fields that can currently be updated with Pop Update and the bind record for the records.

• Record (bind record)
  • Field
• ADM_APPL_RCR_CA (SCCPU_APRCR_BND)
  • RECRUIT_SUB_CAT
• ADM_PRSPCT_CAR (SCCPU_PRSCR_BND)
  • FIN_AID_INTEREST
  • HOUSING_INTEREST
• INAS_CALC_RECS (SCCPU_STDNT_BND)
  • CALC_STATUS
• INAS_FED_EXT (SCCPU_STDNT_BND)
  • BUDGET_DURATION_A
  • BUDGET_DURATION_N
• INAS_PROF_EXT (SCCPU_STDNT_BND)
  • PE_BDGT_DUR_A
  • PE_BDGT_DUR_N
• ISIR_CONTROL (SCCPU_ISIRC_BND)
  • CORRECTION_STATUS
• LOAN_ORIG_DTL (SCCPU_LNORD_BND)
  • LOAN_PROC_STAT
• PELL_ORIGINATN (SCCPU_PELOR_BND)
  • PELL_ORIG_STATUS
  • PELL_TRANS_STAT
  • UPDATE_PELL_ORG
• PELL_ORIG_DTL (SCCPU_PELOD_BND)
  • SFA_COD_CITZN_STAT
• PERS_INST_REL (SCCPU_PERS_BND)
  • Looks like most fields
• SFA_ASG_ORG_DTL (SCCPU_ASGOD_BND)
  • SFA_COD_CITZN_STAT
• SFA_EASTAGE_DTL (SCCPU_EADTL_BND)
  • SFA_EA_PROC_STAT
• SFA_SLC_STUDENT (SCCPU_SLCST_BND)
  • SFA_SLC_P_ATT_IND
• STDNT_AID_ATRBT (SCCPU_STDNT_BND)
  • 31 fields including…
  • AID_APP_STATUS
  • FA_SS_AWD_SECURITY
  • FA_SS_INQ_SECURITY
  • LN_EXIT_INTER_STAT
  • LN_INTERVW_STAT
  • PROCESSING_STATUS
  • QA_SELECTED
  • QA_VERF_SELECT
  • SAT_ACADEMIC_PRG
  • SFA_REVIEW_STATUS
  • VERIFICATION_STATUS
  • VERIF_STATUS_CODE
• STDNT_AWARDS (SCCPU_STAWD_BND)
  • LOCK_AWARD_FLAG
  • SFA_EA_INDICATOR
• STDNT_CAREER (SCCPU_STDCR_BND)
  • SAA_SYNCHRONIZE
• STDNT_CAR_TERM (SCCPU_CARTM_BND)
  • COUNTRY
  • ELIG_TO_ENROLL
  • EXT_ORG_ID
  • FORM_OF_STUDY
  • OVRD_BILL_UNITS
  • OVRD_INIT_ADD_FEE
  • OVRD_INIT_ENR_FEE
  • OVRD_TUIT_GROUP
  • PROJ_BILL_UNT
  • STUDY_AGREEMENT
  • TUIT_CALC_REQ
• STDNT_EQUTN_VAR (SCCPU_EQUTN_BND)
  • VARIABLE_CHAR1-10
  • VARIABLE_FLAG1-10
  • VARIABLE_NUM1-10
• STDNT_FA_TERM (SCCPU_FATRM_BND)
  • BUDGET_REQUIRED
  • FA_STATS_CALC_REQ
• STDNT_PKG_VAR (SCCPU_STDNT_BND)
  • RATING_CMP1-20
  • RATING_CMP_VALUE1-20
  • VARIABLE_CHAR1-10
  • VARIABLE_FLAG1-10
  • VARIABLE_NUM1-10

Process in pictures
(click for full size)

Good luck with Pop Update.

Time to create that new aid year!  CPS starts processing 2011-2012 FAFSAs on Monday (Jan. 3rd).  This will only be my third time setting up a new aid year and my first time in PeopleSoft 9.0 (in a production system obv.).  I have a good reference document from previous setups, a presentation from Alliance 10, and the Bundle 19 documentation at hand to help me remember everything.  Does anyone have a good checklist or anything that you use to remember all the steps and track your progress that you would care to share?  My favorite thing about 2011-2012?  NO MORE ACG/SMART!!!

Happy New Year and as always, good luck.

2011 – The Year of External Awards and File Parser

Not very catchy is it?  I’ll have to work on the marketing aspects of it, but the premise is sound!  2010 (post upgrade) was the year of the 3C Engine and Population Selection.  Those tools have had and will continue to have a big impact on improving our processing efficiency.  The next step to enhance those processes now using 3C Engine with Population Selection is to fully automate the process by adding a COMGEN step.  I have yet to convert a process to use COMGEN but hope to very soon.

We will continue to automate processes with 3C Engine but I think the next big impact will come from further use of External Awarding.  We’ve been using external awarding with fee waivers for a couple of months now and from our perspective it is going very well having improved a previously very labor intensive process.  I’ll qualify that by adding that there is a meeting currently scheduled to discuss the process in which we will find out how the other effected offices perceive the change.  I am now looking at using External Awarding with other award types to eliminate double entry and improve processing.  We just did our first test of adding to the External Awards staging table with a File Parser File Mapping ID and I’m very happy with the results.  I envision accepting award rosters delivered in Excel or sourced out of a web application that will then be seamlessly staged using this process.  Setting up the File Definition was a little challenging so feel free to contact me or post something to HEUG if you’d like some further tips.

As always, good luck!  And happy holidays!

**Update**
It’s only fair to point out that we received lots of great guidance about External Awards and even File Parser from our excellent consultant Jerry!

Now that my class has ended for the semester, I’m able to finally do some other things, like blog.  In the last post I said that my favorite new toy with PeopleSoft 9.0 is the 3C Engine with Pop Select.  If we did not have a customization for it under 8.9, a very close contender for favorite thing in 9.0 would be the mass assign and release of service indicators.  For us in Financial Aid, we use service indicators with many different processes including holding disbursement.  Below are the basic steps to setup and use mass assign and release of service indicators.

1. Set up the Service Impact – Set Up SACR > Common Definitions > Service Indicators > Service Impact Table
   • The impact is what will be referenced in the other processes like disbursement.
2. Set up the Service Indicator – Set Up SACR > Common Definitions > Service Indicators > Service Indicator Table
   • Here is where you will choose the service indicator code, the description (this is what the student will see in “Holds” in Self Service), if the indicator is Positive (if not checked it’s negative), if it should be displayed in self-service (if not checked it will not), the impact you’ve set up previously, and other default options.  You must also assign at least one reason code.
3. Write the query that will be used to select the population to assign the service indicator to.
   • *IMPORTANT NOTE* If the query does not select any rows the process will insert a row into the SRVC_IND_DATA table with a blank EMPLID.  So to keep this from happening, on each of our assign queries I have a union that always and only selects an EMPLID that is not associated with a real person (an account we set up just for processing). If you are going to be running this on a schedule you will really want to consider this step.
   • Your query will need to include the bind record for this population select process which is SCC_BND_SRCIND.
   • I would recommend that your logic include a “does not exist” subquery to make sure that each record is not already assigned this service indicator for this term.
4. Set up the run control – Campus Community > Service Indicators (Student) > Mass Assign
   • Selection Tool: PS Query
   • Select the query: If you do not see it on the list you probably did not include the correct bind record.
   • Previewing the selection results is probably wise the first time and set the query prompts (Edit Prompts link) if there are any.
   • Specify the service indicator code, Reason, Effective Period, Assignment Details, and Contact Information.
5. Run the process.  We have several assign and remove processes running in a JobSet before disbursements each night.
6. Set up Mass Release query and run control if appropriate (include a union for processing ID).

I know this was really high level, feel free to hit me up in the comments with any questions.

Good luck!

My favorite new toy with PeopleSoft 9.0 is the 3C Engine with Pop Select.  We had been using a customization to assign communications with a query in 8.9 but did not have similar functionality with checklists until now.  Being able to assign checklists (and/or communications/comments) in batch based on a query is very useful functionality to have and pretty easy to setup and use.  Below are the steps to setup and use the 3C Engine.

1. Setup the Checklist and Checklist Item (or Communication/Comment) – Set Up SACR > Common Definitions > Checklists > …
2. Setup the Event Definition – Campus Community > 3C Engine > Set Up 3C Engine > Event Definition
   1. Bonus: You can specify a Responsible ID and Item status at this point.
3. Assign a 3C Group to the Event – Campus Community > 3C Engine > Set Up 3C Engine > Event 3C Groups
4. Write the query that will select the group you want to assign the Checklist (and/or communication/comment) to – Reporting Tools > Query > Query Manager
   1. Be sure to join in the Pop Select record for the Administrative Function associated with the event.  For me it is FINA and the record is “SCC_PS_FINA_BND”
   2. Please test your query and check the results!  You really don’t want to mass assign to the wrong population (although with Bundle 19 you can delete 3C’s now 🙂 ).  You’ll probably want to include a “Does Not Exist” subquery to keep the process from assigning the item(s) to the same group every time you run it.
5. Setup the 3C Engine run control.
   1. Select “Population Selection”, specify Event Selection items including the Event ID we created, select PS Query as the Selection Tool, then select your query.  If your query doesn’t show up, you probably did not join in the correct Bind record.
6. Enjoy!

Adding this process to your nightly schedule, especially before disbursements for Financial Aid, could be a very useful tool for your office.  Best of luck with 3C Engine!

A week ago when I was watching the new show The Walking Dead on AMC I thought they were referring to the zombies as Geeks, and this week’s episode confirmed it.  What’s up with that?  Why call the zombies Geeks while at the same time never calling them zombies?  I have a couple of thoughts.

• They are just desperate for any name other than Zombie and Walker is a bit boring to use all the time.
• It seems that Geeks (in the sense that I use the word in this blog) are more interested in Zombies than the average person and they are just trying to reinforce the connection.
• The writers just don’t like Geeks, creative types vs. logical types.
• It’s explained in the comic book if I’d just bother to Google it.

I wonder if there is a blog for people that walk for exercise that has an issue with the “Walker” name?  It’s a good TV show, even my non-zombie obsessed wife is watching and enjoying it.  Zombie movies that I’d recommend include 28 Days Later (while not technically zombies they are very similar except that they are really fast which makes them more terrifying), Zombieland (part comedy), Shawn of the Dead (spoof), Night of the Living Dead (the original).  I don’t get to watch a lot of zombie movies so I’m sure I’m missing some big ones.  Feel free to sound off in the comments with your favorite zombie movie or “Geek” theory.

PeopleSoft Query Tip of the Week.

Disclaimer:  This may only work with Oracle databases

Did you know that you can use the LAG function in an expression to reference a field in a previous row?  It’s true!  I’ll give you a useful example of this functionality.

Expression Text

CASE WHEN LAG(A.EMPLID,1) OVER (ORDER BY A.EMPLID) = A.EMPLID THEN ‘N’
ELSE ‘Y’
END

In English: When this EMPLID is equal to the previous EMPLID then the result of this expression is ‘N’ (not unique), otherwise ‘Y’ (it is unique).

You can now use this expression as a field to display a ‘Y’ when the EMPLID changes or an ‘N’ when it is the same as the previous row.  Alternatively you could output a 1 or 0 (zero) which could then be summed to get a count of the unique IDs in the results.  This is useful for me when I am listing, for example, all of the awards a student has.  The query is returning multiple award lines per ID.  I can use this expression to get a unique count of students on the listing.

Another tool for your PS Query toolbox.  Enjoy.

One of the tools that I find very helpful when working with large CSV or flat text data files is Notepad++.  It is a free text editor program with some really nice features.  The feature I use the most is the Replace function.  I know, every text editor since Windows Notepad has a replace function, but the one in Notepad++ is super fast.  Have you ever tried to replace a couple hundred instances of something in Windows Notepad?  It takes minutes, you can actually see it happening.  Notepadd++ handles thousands of replaces in seconds and can even replace text in multiple files at the same time.  Notepad++ has spell checking capabilities as well which is something you won’t typically find in such a light-weight application.  Another nice option is context highlighting.  You can see in the image on the right that it not only color codes the HTML tags but it also identifies the closing tag when I click on the opening tag.  I don’t know how many languages Notepad++ supports but it looks to be at least 50.  Notepad++ also supports plugins which are extensions possibly programmed by others.  One plugin that I find really helpful is the compare plugin.  With the compare plugin you can compare multiple files and any differences will be highlighted.  Hopefully you will find this information useful and Notepad++ will make your life a little easier.  Cheers.

This seems like kind of an odd first real post topic, but it’s something I am working on right now so it’s front of mind and has some pretty good PS Query tips in it.

Background

As part of our participation in the Quality Assurance Program we have a need to upload files to the Department of Education’s online ISIR Analysis Tool.  I created a PS Query to generate the flat data file in the format required by the ISIR Analysis Tool.  In short the format is one line per student including the Social Security Number, the first two characters of the last name, the initial and paid on ISIR transaction numbers, and a ‘Y’ if the student was selected for verification by the school.  We do not supply the ISIR transaction numbers in our file, we have the Analysis Tool use the lowest and highest transactions for comparison, so you will not see those fields in the following logic.

The Logic

The first step is to create a PS Query with whatever selection logic needed to get the correct population of students to submit.  We used a Checklist to track the students selected for verification.  The following expression can then be used to generate the data in the format for the Analysis Tool.

CASE WHEN B.CHECKLIST_STATUS = ‘C’ THEN CONCAT(CONCAT(D.NATIONAL_ID,SUBSTR(E.LAST_NAME_SRCH,1,2)),’    Y’)
ELSE CONCAT(D.NATIONAL_ID,SUBSTR(E.LAST_NAME_SRCH,1,2))
END

The three keys to this logic are the CASE statement, the CONCAT function, and SUBSTR.  The CASE statement works like IF-THEN logic.  If the Checklist Status is ‘C’ then four blank spaces (for the transaction numbers) and a ‘Y’ will be included at the end of the line, otherwise the field will only have the SSN and name code.  The CONCAT function connects two strings together in a single field.  Since the CONCAT function only takes two strings we need to use two of them (nested) in the first CASE.  Finally the SUBSTR function will shorten a string.  You specify the string to shorten, the starting character position, and the number of characters to include.

The Result

The resulting data looks like this:

123456789JE    Y
987654321AB
555555555HI    Y

Use the expression as the only field in the query output and you have a file ready for submission.  Getting familiar with CASE, CONCAT, and SUBSTR is a really good idea too as those are probably the functions that I use the most with PS Query.  If there are any questions, feel free to leave a comment on this post.  Cheers.

I use the word “Geek” to describe anyone who is knowledgeable, bordering on obsessed with a subject matter.  I’m a geek.  You’re probably a geek about something too.  This blog is about using technology on campus for administrative processes.  My particular areas of interest are:

• Financial Aid
• PeopleSoft
• Queries / Reporting
• Free / Open Source Software Tools

Full Disclosure:  I have started several blogs in the past, all of which were eventually neglected and deleted.  However this is the first that is related to what I do at work.  Hobbies and other interests come and go but I’ve been in Information Systems for 13 years and Financial Aid for 8 so I am expecting this to be a longer surviving blog.  Time will tell I guess.

I have lots of ideas for what I will blog about.  For example, I feel like I have information/expertise related to PeopleSoft query that others will find valuable.  I invite anyone who is a “geek on campus” to post comments, questions, or suggest topics at any time.

Cheers,

John