Queries whose name begins with joip. Primary contacts are Jackie Ciccone cicconjb@jmu.edu , Agnes Choi choiek@jmu.edu. Section last updated 3/29/2018. The interface between Terra Data and sprd exists via two SQR’s named jsa0038 and jsa0039. They run daily via a CRON script at approximately 5 AM.
General Comments
There are several (sometimes conflicting) business processes in use related to “abroad courses”. Generally speaking, these fall into three categories:
Semester/term abroad. Is defined as a student taking ALL of their coursework at a location other than the main campus, normally outside the USA. Term based abroad students are identified by two pieces of data keyed by Agnes Choi. Both fields are located on the stdnt_car_term record. First, the form_of_study field will be set to a value=ABRD. Second, the study_agreement field will contain a value with the first two characters=SA for Study Abroad followed by letters indicating the location. For example, a value=SALONDON indicates the student is studying abroad for the given term in London.
Spring Break Abroad. Defined by the student group description and course requirement description beginning with the 9 characters=”SB Abroad”.
Short term Summer Abroad programs.
The life cycle of a Summer abroad course
Faculty member submits a form to the finance folks about a program, once accepted, the OIP is contacted and works with the admin person to create the course. Admin folks have a doc about entering course data which has specific steps for study abroad courses. Although the doc covers how to enter a requirement which would prevent students you do not want to register for a given course (such as a studies abroad course) there is no specific mention that a requirement must be created for a Summer abroad course and it’s description must begin with the 9 letters=Summer SA
OIP receives info that a student is interested in one/more Summer programs, they send a spreadsheet with emplids to UBO who enters data into Item_sf indicating a deposit is required for official reservation in the Summer program(s). Brandon Cline says two different item_type values are used: ('010000050201','010000050200'). ALSO, in the ref1_descr field on the item_sf record, Brandon loads the name of the abroad location provided him by Agnes Choi when she asks that the program fee be applied to a students account. This is a crucial piece of data as it is used for those few people who participate in more than one Summer abroad session else there would be no way to distinguish the program fee of one location vs. another. Unfortunately, as of 5/20/2011, data is not consistent in this field, and queries such as jfin001 and jfin001b cannot be changed to use this field as the official program because the values are blank, different case, different spelling, etc.
OIP runs a series of joip001 queries to check who has paid their deposits and communicates with the students
Once a deposit is paid for a Summer program, OIP adds the student to a specific student group whose description begins with the 9 letters=Summer SA
Student registers for the Summer course; only students in the specific student group are eligible (via a group requirement attached to the course) to register for the course
OIP runs rosters (via query) of students enrolled in Summer programs. They currently keep manual track of the courses being taught and run rosters one by one based upon this data. They could be using a query such as joip002 which returns all students in a specific students group whose description begins with Summer SA by a specific effective date.
Queries joip004 and joip005 were developed to return Summer abroad courses and students enrolled in same. 3/10/2016: added logic to both queries after Lauren Franson shared that they needed to begin tracking the course requirements in a new record in addition to the original record. The new record is named class_rsrv_cap.
A query could be developed to find students who are active in a student group but are not enrolled in a course whose associated group requirement does not=Summer SA
One confusing point is for the few term based programs which run in the Summer. Unfortunately, the term based programs for the Summer are NOT keyed the same s for Spring and Fall. Specifically, the data entered for Fall and Spring on the stdnt_car_term record (into two fields, see point 1a above) is not entered for the term based Summer programs. As a result, these few programs appear in query results which are supposed to represent just short term Summer abroad programs. Although there are ways to fix this issue, it is being deemed OK for now for folks like Mary Williams to simply ignore such data returned by the query.
3/23/2016: modified the entire query series after Lauren Franson shared that they are now using two different methods to track course requirements. The old location is in class_association, the new location is in class_rsrv_cap. This change was necessary due to issues being caused using the class_association method.
Query details
The joip001 queries used to be named jus0040 and were used primarily by Jackie (as far as I know anyway)
joip001 Study abroad data. The base population of this query is financial data. Specifically, the query selects from those students who have one/more of three specific item types as placed by the UBO. The three items types are for when a student will be spending an entire TERM abroad. You will not necessarily find Summer abroad students returned by this query, unless there is an entire Summer semester being taught abroad. The query contains a LOT of data as requested by Jackie Ciccone over the years. It was written for Jackie to be able to look at all semester abroad students to determine their overall status of eligibility.
joip001a Study abroad data no residency. Almost identical to joip001, except without the official residency data. The design of residency data can cause multiple rows of data to be returned per student.
joip001b Study abroad data history. Was specifically written to allow you to look BACK in time as defined by the fact the students may not still be an active student @ JMU. The two previous queries REQUIRE that the student still be taking courses @ JMU
joip001c Student Health Insurance. A simple query which returns a list of students enrolled in a prompted for term and the status of their health insurance as defined by two specific item type numbers.
Joip002 Summer abroad sdtnts by date returns a list of students in a student group whose name begins with the 9 letters=”Summer SA” and who were added to that student group between the two prompted for dates. Agnes Choi keys the student group data and should know the proper date range to use when running this query. The query is written to return data as far back in history as you’ve keyed data. Want to know who was in a Summer abroad group in 2001? Enter a range of 3/1/2001 thru 6/1/2001 as an example.
Joip002a SA by date aggregate. Same logic as joip002, this version returns aggregate results to allow Agnes Choi to validate the mass upload of student group data she performs each summer for all of the summer abroad locations.
joip003 Students studying abroad/term. This query returns data for TERM BASED studies abroad students, NOT Summer abroad students. To find those not registered for any courses for the given term, look for a value of zero in the last column of this query. Again, this query should NOT be used for Summer abroad programs.
joip004: List of Summer abroad courses. A summer abroad course is defined as any course with an active attached requirement group (see Kelly Downey in the office of the registrar if questions) whose first nine characters=”Summer SA”. These requirement groups are meant to be used by departments via the class_assoc record tied to the appropriate requirement group number. If used in this manner, it prevents students who should NOT be allowed to register for a course being taught abroad from registering for the given course. The other piece of data which ties this all together is the student group entered by Agnes Choi. When a student indicates they are going to take a summer abroad course, Agnes adds them to the appropriate student group by adding and activating a stdnt_grps_hist record. 3/23/2016 update. This logic has changed as a result of the registrars office (Vanessa Breeden) and OIP decision that there HAD to be an option to allow one requirement to be used by more than one course and study group. So, this query was modified to use the instruction_mode=AB (or location=ABRD) logic after verifying same with Lauren Franson.
Joip004a: NON Summer Abrd courses. Is the opposite of the data returned by joip004. Prompts for a term and returns all courses which do NOT have a requirement associated with the course (via the class_assoc record) whose description begins with the 9 letters=”Summer SA”.
Joip004b: TERM abroad courses. It prompts for term and returns TERM based study abroad courses defined IF their location=ABRD OR the instruction mode=AB. I believe there may be a few courses coded this way which are NOT your courses. All I can say is you might contact the “offending” department and ask them to remove the value causing the row to be returned by the query.
joip005: Summer abroad enrollment. Is really an extension of joip004 with students added. Same logic used regarding what constitutes a Summer abroad course. IF the student is not enrolled in any courses for the Summer, they will not appear in the query results.
joip006: Matched and mismatched short term data. Written for Kelly Downey, Agnes Choi and Lauren Franson to determine if there are matches and mismatches between student groups and course requirements. This data is required to match in order to run the jfin001 query for Mary Williams in student accounting who does reconciliation of the short term (aka summer study abroad) program data. Pay special attention to the first field. A value=Missing Student Group, indicates there is a course requirement group setup but no associated student group with the same name. Likewise, a value=Missing requirement group is an indication there is a student group but no matching course requirement. On 9/23/2011 a new population was added to the query to meet a new business need. Lauren needed to be able to email the department secretaries of which course requirement they should enter for the courses being taught abroad for a given term. As of 9/23/2011, OIP has decided to treat all of the term based studies abroad data (student groups and requirements) the same as they do for Summer abroad courses. Rows may appear if a specific program is not being run at the current time. You can remove such data my inactivating the offending student group or course requirement. All three joip006 queries have a prompt. To return all groups/requirements, leave the prompt blank. Enter “A” if you only want active data, “I” for inactive data
Joip006a and joip006b. As the query description implies, the A suffixed query is meant to be run by Kelly Downey. It returns all courses for a given term with a course requirement whose description begins with the nine letters=”Summer SA” or “Studies A”. The B suffixed query returns active student groups whose description begins with the nine letters=”Summer SA” or “Studies A”.
joip007 Term abroad students. Is mostly a repeat of joip003, with fewer fields. The query will only return TERM BASED study abroad students, NOT Summer abroad students.
joip008 SA students with credits. In this case, the SA stands for Summer Abroad. This query should ONLY be run for Summer abroad students. If the last field is blank, it means the student has been entered into the student group by Agnes Choi, BUT they have not yet enrolled in any courses being taught abroad for the given Summer term. You should only run this query for Summer terms (last digit=5). The date range is the same as in query joip002. It indicates the date Agnes Choi keyed the student into the respective Summer abroad student group.
Joip008a: SA students with crd details. Only difference from joip008 is an additional prompt for a single emplid. Allows the user to view the query results and determine WHICH of the courses the student is registered for are NOT tied to a course requirement (the RQ group field will be blank) which needs to be corrected. It indicates either the student registered for the wrong section of a course, or the course needs to have a requirement associated with it.
Joip009: Avg term to graduate OIP. 8/3/2012: cloned from jusdh008 for Lauren Faulson in office of international programs. Wanted stats on graduation rates for specific populations of students studying abroad. The populations could not easily be defined, so student groups were created and records added to represent the various groups. 8/13/2012: Lauren called and wanted the average number of years added as a field. For accuracy, used the academic years for the admit term and the grad term
Joip012: Service indicator data. Prompts for a specific service indicator code and reason and term. Returns a list of students who meet this criteria. Original intention was to allow user to return those students who have received study abroad health insurance. Originally created for Jessica Hazlewood who has the values for the prompts.
Joip013: study abroad by acad year. Selects study abroad (both term and summer) students and returns aggregate data for ALL abroad locations. Written for Jackie Ciccone.
Joip013b: This version cloned and modified for Lee Sternberger. Wanted aggregate data to determine the percentage of UG graduates who have studied abroad for a given academic year range. Removed stdnt_car_term and used the count distinct aggregate function to ensure the most accurate results. Verified with registrars office they do NOT delete old student group descriptions nor change their first nine characters from “Summer SA” which the query relies on for Summer study abroad results.
Joip014: Graduation rates. Selects 3 baseline populations via unions and allows comparison of the aggregates. Allows one to infer whether or not studying abroad is a cause for higher/lower graduations rates vs. the baseline entire UG population.
Joip015: terms to graduate ABRD. Prompts for an academic year range. Selects detail data which can be taken into excel to determine the average number of terms for the two populations to graduate from JMU.
Joip016: Never studied abroad. Prompts for a single term, returns student identifiable data of students who did NOT study abroad ever nor did they ever attend a summer abroad session.
Joip016a: This version is used to return a baseline population of all seniors for the prompted for term. Results to be used in conjunction with joip016.
Joip017: Term abroad locations 4ever. No prompts, returns aggregate data to identify those students who have term studied abroad from forever @ JMU.
joip019 Spring Break Abroad students. This query is similar to joip008 with the baseline population being those students who are active in a student group whose description begins with “SB Abroad”. The date range is the same as in query joip002. It indicates the date Agnes Choi keyed the student into the respective SB Abroad student group.
joip020 Applied to graduate (TS): Prompts for an expected graduation term. Returns those students studying abroad (Summer or term) whose expected graduation term matches the value entered in the prompt and who have officially completed the necessary paperwork with the office of the registrar to graduate.
joip021 Ever studied abroad, term. Contains no prompts. Returns a list of any/all students whose stdnt_car_term study_agreement is greater than blank. Joip021a returns similar results for any student who had ever studied abroad for a summer term.
joip022 Forever VISA data. Returns visa permit data for any student who ever matriculated.
International Student Fee Charge Process (as a result of a meeting in OIP on 10/24/2012)
General comments
International students are currently being charged a $75 per semester administrative fee. One purpose of the meeting was to streamline the process of placing (and removal) of this fee. A new fee is being implemented for the Spring 2013 semester. The purpose of this fee is related to health care insurance. The base population is anyone with a visa permit type which begins with an F or a J who are active in their academic program, are not in the study group.
One time tasks
Jessica will ask the office of the registrar to create two new service indicator codes and service indicator reasons. For the purpose of this documentation, I will refer to these as service indicator=010 and the reasons=00301 for the admin charge, and 00302 for the health insurance charge.
Pete will create a query named joip010 with the results to be used to create service indicators per the following specifications.
Include students active in their program whose highest effective dated visa permit type begins with F or J
Eliminate students in the study group
Eliminate students if they already have a charge on the item_sf record for the prompted term and item type provided: 000049000 – Health Insurance Fee and 010000050620 – IA Administrative Fee
Eliminate students if they already have a service indicator code=010, reason=00301 (admin fee) or 00302 (health insurance) prompted for
Allow elimination if the country of citizenship is a prompted for value, such as SAU to eliminate Saudi Arabia students for the run of this query when creating service indicators for the health insurance charge
Pete will create a query named joip011 with the results to be used to place and remove charges per the following specifications.
No need to repeat logic in step 2 a-e above, because we know the service indicator data will only be present for the given populations. Therefore, drive this query off of the service indicator raw data.
It will be based upon students active in their academic program, who have either of the service indicators 00301 or 00302. Contrary to original specs, the query will NOT include a prompt for a date range. It will include logic to ensure the specific charge or refund does not already exist in item_sf. Logic gained after a conversation with Brandon Cline. This will be a union query. The first selection will include charges to be placed on a students record. The second selection will be for charges to be removed from a students record.
Brandon provided the item types to be used for placing and/or removing these two charges: 010000049000 – Health Insurance Fee and 010000050620 – IA Administrative Fee
Ongoing tasks
At least once a semester for both Fall and Spring
Jessica will run the joip010 query twice:
once using a service indicator reason=00301 entering an appropriate value for the term, leaving the country prompt blank and a value of 010000050620 for the service indicator reason prompt.
a second time using a service indicator reason=00302 entering an appropriate value for the term and entering the value=SAUD in the country prompt. Doing so will ensure a service indicator is NOT created for the Saudi Arabia students. If there is no service indicator row, the charge will not be created later. For this run, use a value of 010000049000 for the service indicator reason prompt.
Send the results to a spreadsheet named 00301.xls and 00302.xls respectively.
Use the service indicator mass assign process to assign the respective service indicators to students using the two files created.
Enter an appropriate value in the amount column corresponding to the fee to be charged for each of the two charges.
Leave the reference field empty/blank.
Enter the proper value in the active term field.
Repeat these steps as often as you want to ensure all students have the appropriate service indicators.
Due to the design of the joip010 query, it will not allow you to add a service indicator which is already present for a given student. In other words, you are not going to hurt anything by following this step every day, or once a week, or however often you choose.
Whenever it is determined a student should have either/both charges waived, Jessica will delete the respective service indicator from the individual student. A service indicator should never be deleted in a given term after step 4 has been executed else the resulting charge will not be removed from the student’s record.
Whenever it is determined a student should have the charge amount changed, Gina Kudrav will contact Jessica with the SA Emplid of the student, which of the two charges to adjust, and the adjusted amount. Jessica will edit the appropriate service indicator record with this adjusted amount. This step should not be executed in a given term after step 4 has been executed. Instead, Gina Kudrav will email the UBO with the following information and ask the UBO to adjust the charge: SA Emplid, term, item type, adjusted charge amount.
Create initial charges.
Timing for executing this step is crucial. Once executed, steps 2-3 above must not be performed for a given term. Choose the date to execute this step with this in mind.
Run query joip011 twice, once using a combination of 00301/010000050620 for the service indicator reason and item type and a second time using a combination of 00302/010000049000 for the service indicator reason and item type.
Send the results to an excel spreadsheet on the shared drive accessible by UBO. Use a naming convention agreed upon with the UBO.
Follow your business process to notify the UBO charges and refunds are ready to be created.
Adjust charges.
When it is determined a charge should be removed, edit the appropriate service indicator record for the student. In the assignment details section, in a field labeled “Reference:” enter the word “REMOVE” to indicate the respective charge should be removed for the given student. You must also enter today’s date in the field labeled “End Date”
Under no circumstances should the amounts on the individual service indicator records be adjusted once step 4 has been executed. If a charge needs to be adjusted, refer to the logic is step 3 above. Editing an individual charge amount on the service indicator record after the charge has been placed on a students record will have no affect on the actual charge.
Repeat step 4 above; this step can be repeated as often as desired. It will create new charges as well as charges to be removed. If neither step 1 nor 5a have been executed, repeating step 4 should not produce any results. A file with no results should not be created, and the UBO should not be contacted.
If preferred, charges for new students can be created by manually adding the appropriate service indicators. This bypasses step #1. Such charges will still be picked up by executing step #4 as that step has no logic to determine if a service indicator was created in mass or one at a time via manual entry.
