I just submitted my first session proposal for a conference!  I submitted a session called “Fast SAP processing with jobsets, 3Cs, service indicators, online appeals, and web services” for the Alliance13 conference this March.  I will be attending the conference regardless of if I am presenting or not.  This will be my second Alliance, having attended the 2011 conference in Denver.  Wish me luck!

Cool New Tool: Drilling URL

In starting loan origination for 2011-2012, I wanted to know what status all of the loans were left in after running the process.  So I created a really quick query to just count the IDs by loan status for the aid year.  When I saw that some loans were in a “Hold” status I wanted to then get the listing of those IDs so that I could go and look at those accounts.  This reminded me of the drilling URL example from the Maximizing the use of PS Query for Financial Aid session from the Alliance 2011 conference.  After setting up the Drilling URL, I am able to just click on the loan status and see a listing of all the loans in that status.  There are lots of examples that I can think of that this technique would be useful for.  A department could show the count of students in their classes for a semester and click the class name to show a roster of students in the class.  I will now step you through setting up a query with a Drilling URL.

A drilling URL actually connects two different queries.  So the first step is to create what I will refer to as the detail query, the query with the specific listing that will be reached by clicking the link in the top level query.  In my example this is the BG_FA_LOAN_STATUS_DETAIL query.

For my detail query I am just going to output a few fields from the LOAN_ORIG_DTL record along with the student’s name.  The first important step is to create the Prompts in this query that will be part of the linking process from the top level query.  I want to list the details for all of the loans in a given status for a given aid year, so my two prompts will be for Aid_Year and LOAN_PROC_STAT.

Of course these prompts must be in the Criteria of the query in order to be used.

Next, save this query and begin a new query.  My top level query is called BG_FA_LOAN_STATUS.  It will display a count of records in each loan status by aid year (prompting for aid year).  You’ll see that I have a “Count” on A.EMPLID and I am displaying LOAN_PROC_STAT twice.  This is because I want to display the long translate of the field for the user (note the ‘L” in XLAT) but I also need the one character code to use in the prompt of the detail query.  To do this I just created an expression that is simply A.LOAN_PROC_STAT, displayed that expression and used the translate on the actual field.  The aid year is also displayed and will be used for the detail prompt.

Now you need to setup the Drilling URL.  In “Expressions”, click the Add Expression button and select “Drilling URL” from the Expression Type drop-down.  (the expression text will get filled in for you after completing the following steps).

There are different Drilling URLs that you can use, but we want a Query URL, so click the Query URL link.  You can now search for the detail query that you will be linking to.  It will bring in the prompts from that query (URL Keys) and you choose the fields from your query to populate those prompts.  You also choose (in Map URL to Query Columns) which value or values to turn into clickable links, I am only using the translate of the LOAN_PROC_STAT as the hyperlink, but any or all of the fields can be used to the same effect.

That’s it!  Now when you run this query the Status field will be a link that you can click to get the detailed listing.  Let me know if you try this and have any questions.  Enjoy!

I titled this post Grad School because that is why I haven’t been posting.  I’m pretty sure the next 8 weeks are going to be pretty bad too, but I should have a bit of a break after that.  Since the last posting we did get our award letters out on time, hurray!  I’ve also done some kind of interesting things with Pop Update, particularly with having terms building new budgets when an ISIR comes in after a budget already exists.  We’ve also started making much more use of External Award, including “Process SF External Award” and “Process External Award File”.  Along with External Awards, we have begun using the new Repackaging functionality.  Finally, we are now sending e-mails out over night in a jobset using 3C engine and ComGen.  It wasn’t nearly as difficult as I was afraid and wish we would have started it sooner.  When I next come back to post I’ll likely break-down one of these processes and share our setup and implementation.

Cheers,

John

Not that I’m ever the poster boy for reliable blog posting, but I’m probably not going to be posting much until award notification go out in mid-March.  I just wanted to give that warning so you don’t think the blog has been abandoned if there are a couple of weeks without an update.  I do have one little PS Query trick for you today though.  In creating a new Pop Select query for aid year activation I wanted to check for an award offer in the prior aid year.  Sure I could have just prompted for the prior aid year and used that, but I was already prompting for the new aid year and didn’t think both should have to be entered.  The solution was to use the following criteria in an “Exists” subquery:

Best of luck!

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!

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.