PS Query & SQL

I'm working on querying Enrollment Request data to build a running total of a student's units over time. For this, I need to only consider requests that actually went through, but I've found that some of the transactions resulting in "Message" are successful, but not all of them. Is there a way to identify which "Message" statuses are which? I'm hoping I don't have to go one-by-one through every message type we've ever had just to sort through them and come up with a CASE statement...

Thanks in advance!

I hate to burst your hopes... but there is more than just the Message status to be leery about when trying to analyze Enrollment Request records.  I've tried using these as a way of recreating credit counts over time as well and ran into all sorts of issues.  Some may be specific to what a university allows or doesn't allow to be done with Enrollment Requests and others may be how the system is configured for handling retention of dropped classes in STDNT_ENRL.  Enrollment Requests can sometimes be opened and reprocessed which can change the assigned status and message.   Proper handling of swaps and Auto Enrolled classes also need to be taken into consideration.

If it helps, when it comes to Messages with a MSG_SEVERITY of M, I only came across Message Set Nbr 14640 and 14641, Message Nbr 154 as a successful enrollment.

It has been a while since I have attempted this.  I got a lot to match, but could never get to the point where I could get a 100% match on all terms I ran it against.

Hopefully you have better success than me.

I'm working on querying Enrollment Request data to build a running total of a student's units over time. For this, I need to only consider requests that actually went through, but I've found that some of the transactions resulting in "Message" are successful, but not all of them. Is there a way to identify which "Message" statuses are which? I'm hoping I don't have to go one-by-one through every message type we've ever had just to sort through them and come up with a CASE statement...

Thanks in advance!

I'm working on querying Enrollment Request data to build a running total of a student's units over time. For this, I need to only consider requests that actually went through, but I've found that some of the transactions resulting in "Message" are successful, but not all of them. Is there a way to identify which "Message" statuses are which? I'm hoping I don't have to go one-by-one through every message type we've ever had just to sort through them and come up with a CASE statement...

Thanks in advance!

We have created a way that has worked for us at UC Berkeley with a series of 10 views to develop an effective dated and sequenced tracking of enrollment requests.   As @Mark Miszewski brought up, we are looking at Message Set Nbr and Message Nbr, which means we have had to make adjustments over time to deal with Oracle updates. We started with Message Set Nbr 14640 &14641 and then added 14642 & 14643 with fluid updates.  It works with how our classes and enrollments are set up, but it may not necessarily work for others. The only messages we have found that we need to account for are for the waitlist. Then other scenarios we account for in different views, including separating two-part enrollment on two separate lines.

• UC_ENRLRQDTL_BV - Base View to limit only requests that might lead to something
• UC_ENRLRQDTL_V1 - Simple, straightforward enrollments
• UC_ENRLRQDTL_V2 - Drop if enrolled in a swap
• UC_ENRLRQDTL_V3 - 2nd part of drop if enrolled in a swap
• UC_ENRLRQDTL_V4 - Change to enrolment
• UC_ENRLRQDTL_V5 - 2nd part of change to enrolment
• UC_ENRLRQDTL_V6 - Cancel Class 
• UC_ENRLRQDTL_V7 - Withdraw
• UC_ENRLRQDTL_VW - Union all of View 1 to 7
• UC_ENRLRDTL_FVW - Final front-facing view available in query manager

I'm working on querying Enrollment Request data to build a running total of a student's units over time. For this, I need to only consider requests that actually went through, but I've found that some of the transactions resulting in "Message" are successful, but not all of them. Is there a way to identify which "Message" statuses are which? I'm hoping I don't have to go one-by-one through every message type we've ever had just to sort through them and come up with a CASE statement...

Thanks in advance!

Ross,

Wow, that's a lot of views! A granular approach might end up being more performant than my approach, though. I'll take a look. Much appreciated!

We have created a way that has worked for us at UC Berkeley with a series of 10 views to develop an effective dated and sequenced tracking of enrollment requests.   As @Mark Miszewski brought up, we are looking at Message Set Nbr and Message Nbr, which means we have had to make adjustments over time to deal with Oracle updates. We started with Message Set Nbr 14640 &14641 and then added 14642 & 14643 with fluid updates.  It works with how our classes and enrollments are set up, but it may not necessarily work for others. The only messages we have found that we need to account for are for the waitlist. Then other scenarios we account for in different views, including separating two-part enrollment on two separate lines.

• UC_ENRLRQDTL_BV - Base View to limit only requests that might lead to something
• UC_ENRLRQDTL_V1 - Simple, straightforward enrollments
• UC_ENRLRQDTL_V2 - Drop if enrolled in a swap
• UC_ENRLRQDTL_V3 - 2nd part of drop if enrolled in a swap
• UC_ENRLRQDTL_V4 - Change to enrolment
• UC_ENRLRQDTL_V5 - 2nd part of change to enrolment
• UC_ENRLRQDTL_V6 - Cancel Class 
• UC_ENRLRQDTL_V7 - Withdraw
• UC_ENRLRQDTL_VW - Union all of View 1 to 7
• UC_ENRLRDTL_FVW - Final front-facing view available in query manager

I'm working on querying Enrollment Request data to build a running total of a student's units over time. For this, I need to only consider requests that actually went through, but I've found that some of the transactions resulting in "Message" are successful, but not all of them. Is there a way to identify which "Message" statuses are which? I'm hoping I don't have to go one-by-one through every message type we've ever had just to sort through them and come up with a CASE statement...

Thanks in advance!