Bookstore Financial Aid Process
Using Blackboard Transact
As of 5/14/2021
Original location: The original is at F:\Student Financial Services\Bookstore Financial Aid Accounts Transact
SFS To Do:
In the Jenzabar database there is a custom table called ccr_nuvision_fa_bookstore_semester. Each semester when it is time to open the book store accounts, simply to the table and change the year and term as needed. You can link the table into an Access database. Then open the table and type in a new year and term. Simply moving out of the cell saves the data. You do not have to push a save button.
IT can give access to update the table. A new user needs to be added to the SQL Student Accounts active directory group.
The process looks at the year and term in this table and pulls the students who are registered for that semester.
After you change to the new year use the Customer Retail Transaction Tool to clear out all student’s financial aid buckets. This will remove balances from students who are not currently enrolled if they have any. The next time the financial aid process runs it will put balances on only students who are enrolled with credits on their account. Log into Transact. Choose Customer Retail Transaction Tool. Click Edit beside Financial Aid Zero Out. Click the Run Tool Configuration button to run the process. If you can not edit it, you may have to create your own. Below is what the setup looks like.
The PIN for the Jenzabar Financial Aid cashier is 1111.
Overview:
Send Balances to Transact:
Part 1 - There is a SQL Server Agent job on our Jenzabar SQL server called JEX Update: FA Bookstore Balances for ID Cards. The job creates a file of student id numbers and balances and places it on the Blacboard Transact server called cccdining. The job is scheduled to run every day and every 15 minutes between 8:00 AM and 7:00 PM.
The job contains one step. That step calls a SQL Server SSIS package called SFS ID Card FA Balance. Currently the username for the package is ssispackage and the password is crest1867. The package runs a stored procedure from the Jenzabar database called ccr_sp_ssis_fa_bal_idcard. The stored procedure uses the year and term from the custom table mentioned above and pulls data for every enrolled student’s ID number and AR balance. If they owe money their balance is reported as $0 and if they have a credit on their account that credit is reported. A comma delimited text file is created from the data called Jenzabar_FAD_Import.txt and is placed on the cccdining server in a share called Imports. If a file with the same name already exists, it is configured to be overwritten.
Part 2 – On the cccdining server is a Windows Task Scheduler job called Financial Aid Deposits. This job runs every day beginning at 8:01 AM and is repeated every 15 minutes for 12 hours.
The job launches Blackboard under a username of Administrator using a configuration called Jenzabar Financial Aid Deposits.
IMPORTANT: The password to the Administrator account will expire every 90 days. Penny can reset it. Then the new password must be put in the action details of the task schedule job. To save the job change you must know the password of the Windows account that is the job owner. Currently that username is transactsupport.
You can see and edit the configuration in the Transact software under Customer Retail Transaction Tool. The configuration is currently set to adjust stored value account balance to specified amount. It uses the file created above. Log files for results and errors are created and the original file is appended with a number due to the rename input file after processing option.
VA Accounts: Open the stored procedure mentioned above for modification. Open SQL Server Management Studio from the Cedar Crest Remote Apps group. Log in to jzccr-sql1 using windows authentication. Click Connect.
On the left hand side of the window in Object Explorer expand Databases, TmsEPrd, Programmability, Stored Procedures, and scroll to find ccr_sp_ssis_fa_bal_idcard. Right click on the name and choose Modify. In the stored procedure find the section of code for the semester needed. Each section begins with IF @trm_cde = and then a term code such as IF @trm_cde = ‘10’. In the first select query in the union find the line in the where clause that reads and ID_NUM not in (12345) –Exclude VA students. Replace any ID numbers there with the new number(s). Click the Execute button on the tool bar to save the change. This will prevent the process from sending a balance for the student. Someone can then go into Transact and add the correct balance on the student.
Getting Bookstore Data from Transact Into Jenzabar:
Blackboard wrote a program to bring financial aid purchases and returns back into Jenzabar. The program is scheduled to run using a Windows Task Scheduler job called Jenzabar Bookstore transactions. The job runs at 12:03 AM every day and repeats every 5 minutes for a duration of one day.
The programming uses stored procedures from the Jenzabar database to create a daily BS transaction group and add any transactions from the bookstore. Once a day in the morning it calls the stored procedure called ccr_sp_fa_bookstore_group_num to get the next available group number for BS transactions and update the source_master table in the Jenzabar database with that number.
Each time is runs it calls a stored procedure called ccr_sp_fa_bookstore_trans. This stored procedure accepts the day’s group number, an ID number and amount of the transaction. It uses that data to update the trans_batch_ctl table with the batch info when the first transaction is added. It then creates a transaction in the daily BS batch for the purchase or return. The transaction description is Financial Aid Bookstore Transactions with the current date and time as the transaction date.
Troubleshooting:
If the students do not have the correct amounts in the financial aid bucket on their accounts first check to be sure the custom table has the correct year and term. Next look on the cccdining server to be sure the Financial Aid Deposits job in Task Scheduler is in a ready status. If the Blackboard Transact password expires as mentioned above, the jobs will fail. If the job is disabled, you can enable it but be sure the password in the details line on the action tab has not expired.