USU Registrar’s Office
Oracle
I started working for the USU’s Registrar’s Office in September of 2011. My main tasks is to write SQL queries to get information upon request of USU faculty. An example of a request would be to provide the student ID number, First Name, Last Name, and E-mail of students who are either theater majors or minors. A more complex request would be similar to provide the contact information of students to owe the school money, how much do they owe, what was the last term they were registered for, major and department. These reports and then exported to excel and e-mailed to whom ever requested it.
Another duty is updating and maintaining USU Registrar’s website. USU has developed a program called EZ Plug, which is designed for easy web maintenance by someone who doesn’t know HTML or other web languages. Many things I have needed to do are easily accessible , but more complicated things are not. In those instances, I had insert or modify the HTML code manually.
This was a project for an advanced, student run, laboratory called CEBA, to build professional business applications for Utah State University, the Jon M. Huntsman School of Business, and their stakeholders. It recruits the most promising students in key areas of management information systems and related fields to prepare them to immediately step into highly competitive careers as information systems managers with experience and skills above those of their peers.
S.C.O.P.E. stands for Shingo Culture Online Performance Evaluation where employees in a company would complete an online survey to help describe to management what the culture is generally like. By analyzing a company’s culture and with Shingo’s expertise, Shingo and describe what direction the company will take and can help redirect that direction if needed. This service is generally geared towards medium to large companies with offices on a national or global scale.
Shingo hired CEBA to build the initial survey set-up, survey itself, and reporting tools. I was on a team with two other database analysis to build the backbone of this system. This required many meetings with our stakeholder in order to get a firm understanding of what would be expected from the system and good communication, team-work, and problem solving on my team’s part. After a semester of being part of the Shingo SCOPE team, we produced the database and many stored procedures, triggers, and some video tutorials for users to use the system. Click here for an image of the ER diagram.
We used stored procedures for inputting new inventory and when inventory is shipped out. TIT needed to know the amount of tons for each shipment they were accountable for to their customers. For this, a trigger was made which is shown below.
ALTER TRIGGER [dbo].[updAccountableShipmentReceived]
ON [dbo].[ShipmentsReceived]
FOR update, insert AS
IF TRIGGER_NESTLEVEL() > 1
RETURN
DECLARE @ShrinkWeight AS decimal(10,4)
DECLARE @ShipmentID AS int
SET @ShrinkPercentage = (SELECT ShrinkPercentage FROM inserted)
SET @ShrinkWeight = (SELECT ShortTonsReceived FROM inserted) * @ShrinkPercentage
SET @ShipmentID = (SELECT ShipmentID FROM inserted) UPDATE ShipmentsReceived
SET ShortTonsAccountable = (SELECT ShortTonsReceived FROM inserted) – @ShrinkWeight,
ShrinkAmount = @ShrinkWeight WHERE ShipmentID = @ShipmentID
An interface was made using C# that allows the user to easily input shipments, cargo, and export graphical reports to excel. Currently TIT has plans to use our database as they continue to expand, have more complicated shipments, and want to analyse shipments. The complete report can be found here which has more diagrams and screen shots of the project.
Other Projects and Items of InterestI have participated in a study abroad during the Summer of 2011. Additionally, I quite enjoy making reports, presentation, and other material aesthetically pleasing. Here is a sample of past projects, reports, and a description of my study abroad experience.
Clayton L. Fielding, CSM 
