Posted by jeggent on 12th July 2011
As expected, the previous semester was very busy. The next semester will likely be even busier. However it will also be my last semester. Assuming I am able to complete the two classes, I will graduate this December.
Today I have used the DECODE function for the first time in a PeopleSoft query. I am not sure which databases this function works with, we are on an Oracle DB. Decode allows you to display a specific value based on the contents of a field. For example, if a field contained integers you could output the corresponding letter of the alphabet. The syntax of the function is like:
DECODE(Field, Value 1, Output 1, Value 2, Output 2, Else)
Alphabet example:
DECODE(A.Field, 1, ‘A’, 2, ‘B’, 3, ‘C’, 4, ‘D’, …., 36, ‘Z’, ‘other’)
I am using this function in a report for repackaging aid. I am “decoding” the currently packaged item type to the item type it needs to be replaced with. If there are multiple values that translate to the same output then you may want to consider a CASE statement with an IN list. Maybe I’ll go over that next. Hopefully before Fall semester starts 🙂
Good Luck.
John
Posted in PeopleSoft, PS Query | Comments Off on PS Query – Decode Function
Posted by jeggent on 9th May 2011
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!
Posted in Financial Aid, PeopleSoft, PS Query, Tools | 2 Comments »
Posted by jeggent on 22nd April 2011
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
Posted in Financial Aid, PeopleSoft | Comments Off on Grad School
Posted by jeggent on 26th January 2011
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!
Posted in Financial Aid, PeopleSoft, PS Query | Comments Off on Prior Aid Year
Posted by jeggent on 12th January 2011
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.
- 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”
- The field to be updated must be associated with the record. “Set Up SACR > System Administration > Utilities > Population Update > Population Update Setup”
- 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.
- 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.
- 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)
- ADM_APPL_RCR_CA (SCCPU_APRCR_BND)
- ADM_PRSPCT_CAR (SCCPU_PRSCR_BND)
- FIN_AID_INTEREST
- HOUSING_INTEREST
- INAS_CALC_RECS (SCCPU_STDNT_BND)
- 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)
- LOAN_ORIG_DTL (SCCPU_LNORD_BND)
- PELL_ORIGINATN (SCCPU_PELOR_BND)
- PELL_ORIG_STATUS
- PELL_TRANS_STAT
- UPDATE_PELL_ORG
- PELL_ORIG_DTL (SCCPU_PELOD_BND)
- PERS_INST_REL (SCCPU_PERS_BND)
- SFA_ASG_ORG_DTL (SCCPU_ASGOD_BND)
- SFA_EASTAGE_DTL (SCCPU_EADTL_BND)
- SFA_SLC_STUDENT (SCCPU_SLCST_BND)
- 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)
- 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.
Posted in Financial Aid, PeopleSoft, Tools | Comments Off on First Pop Update
Posted by jeggent on 30th December 2010
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.
Posted in Financial Aid, PeopleSoft | Comments Off on Aid Year Rollover 2012