eTranscripts

Body

Process Overview

eTranscripts processing includes three components:

Transcript Ordering - Students or alumni use the Clearinghouse Transcript Ordering interface to place transcript orders online.
Ellucian Cloud - The Ellucian Cloud acts as the broker for the order. It is responsible for receiving the transcript order information from the Clearinghouse, sending the order to the appropriate ERP system, requesting transcript order status updates from the ERP, and sending updates (when appropriate) back to the Clearinghouse. A set of functions or calls (APIs) are executed during the order process and are used to securely transmit the orders as well as provide status updates for the orders.
ERP - The ERP system receives, automates the processing of, and fulfills transcript orders.

Source: NSC eTranscripts Functional Testing Guide for Banner SIS

Who Should I Contact (NSC vs Ellucian)?

First, review the Banner Troubleshooting Guide.

Next, (if unable to resolve the issue) determine who needs to be contacted.

National Student Clearinghouse:

Customizing your masthead
Changing Transcript Ordering profile settings (pricing, delivery option updates, hold text updates, etc.)
Applying or changing your PDF visible signature
Applying digital signature/blue ribbon to PDFs
Applying or /changing rights management policies to PDFs
Applying or changing the PDF watermark/template
When you notice a transcript order in any of these statuses for more than four hours:
Consent Form Received • Electronic Transcript Uploaded
When the transcript link and/or password was not received (recipient should contact service@studentclearinghouse.org with order #)
When you’ve reestablished your connection to the Ellucian cloud and you need new orders older than 72 hours repushed to you manually – Provide your CMR with order numbers
To ask questions about how to process attachments
To ask questions on Clearinghouse online reports

Note: Please make sure that your transcript processors allow the automated process to run. They should NOT regularly go to the NSC site to status any orders. They should only go to the NSC site to review attachments, run reports and make sure orders are not stuck in the New Requests or In Process.

Ellucian:

Orders Stuck in New Requests – typically means there has been a loss of connection between your school and the Ellucian cloud that you must resolve. This would result in new orders not being able to reach Banner. Ask your IT team to restart your connection to the Ellucian cloud and/or restart your eTranscripts API server. If this does not resolve the issue, contact Ellucian.
Orders Stuck in In Process – Typically means that, similar to the above, your connection to the Ellucian cloud must be restarted. Examples: timing/frequency of purging orders, connection hangups, and/or status in ERP.
Order in Banner has a status of Transmission Failed “TF” – Review Ellucian’s troubleshooting guide to correct issue or open action line ticket with Ellucian if unable to resolve.
Order in Banner has a status of Ready to Generate “RG” Review Ellucian’s troubleshooting guide to correct the issue or open action line ticket with Ellucian if unable to resolve.
Student not receiving “Hold for Grades/Hold for Degree” Options –Typically occurs when Banner cannot find the student as being in a program awaiting grades or enrolled in a degree program. Provide Ellucian with student information to determine why options were not presented to student during order
Cloud UI Ping Test Failed with an Authentication Error – Open Ellucian Action Line Ticket
Paper Transcript Orders are not printing or Paper Transcript Orders are not printing correctly – Work with your IT team and/or Ellucian for resolution
“Hold for Grades/Hold for Degree” orders not processing – See Troubleshooting guide. If that does not assist, reach out to Ellucian for resolution
Questions regarding screens within Banner
Questions as to why a student order went to Needs Research “NR”

NSC Website Links

National Student Clearinghouse Website: studentclearinghouse.org

We suggest that you keep the URLs to our online services below handy, so you and your students can directly access our services should the need arise in the future. Please add https:// to the beginning of each of the URLs below.

Clearinghouse secure site login: secure.studentclearinghouse.org/acc/servicedispatcher
Clearinghouse sFTP: ftps.nslc.org
Transcript Ordering Center: tsorder.studentclearinghouse.org
Transcript Order Status: tsorder.studentclearinghouse.org/TrackOrder
Verification Services: nscverifications.org/welcome-to-verification-services

SHAETAD Configuration

File Transmission Configuration

Host Name = ftps.nslc.org

Username = etran (name provided by NSC)

SSH Directory = E:\SCT\JOBSUB\BAN9\KEYS\ (location you placed file

"id_rsa" on jobsub server. It must end in slash)

Passphrase = etran1 (whatever you used to create private_key file)

Ellucian Cloud Connection

Cloud URL = https://etsapp-stage.10133.elluciancloud.com/ or

https://etranscripts.elluciancloud.com

Cloud Username = etranuser@school.edu

Cloud Username Password = u_pick_it (whatever you used)

Confirm Password = u_pick_it (whatever you used)

EllucianCloud TEST: https://etsapp-stage.10133.elluciancloud.com/

EllucianCloud settings use the following for the ERP URL: https://betrntst.oakland.edu:8101/eTranscriptAPI/

https://etsapp-stage.10133.elluciancloud.com/

Username: etranuser@oakland.edu

Password: (in password protected spreadsheet)

Test Orders

Test link to run test orders:

https://staging-tsorder.studentclearinghouse.org/school/ficecode/00230700

Visa: 4111111111111111, future expiration date, security code 123

Test Admin site for monitoring of test orders (please do not change any statuses on this site and let the automated process run):

https://demo.studentclearinghouse.org/

Username: OUCOORD

Password: (in password protected spreadsheet)

Test sFTP:

https://stg-ftps.nslc.org

Username: 00230700-iv_test

Cloud: https://etsapp-stage.10133.elluciancloud.com/

Order Status = Transmission Failed (TF)

Look at the settings on SHAETAD (top block)
Check that the public and private keys were generated. The public key needs to be shared with the Vendor.
Ensure there is a Passphrase on the keys that were generated. Banner requires a passphrase.
Check that The Passphrase has been entered correctly with no special characters. This is the password for the private key. The Passphrase must be 16 characters or less.
Be sure the SSH directory has a slash at the end of it.
Orders are resent 3 times before they fail.
After the order fails 3 times, it is placed in SHASFTP
Resend the order on SHASFTP
Click the Resend checkbox next to the order to be resent and SAVE.

Order Status = Needs Research (NR)

Problem Statement: We have some orders that come up as needing research (NR) that appear to need an ID verified. Adding the correct ID on SHAETOR does not move the order forward. Why is this?

The NR status indicates an order "Needs Research". In most cases, this status indicates the order has either arrived without a Banner ID and no match was found or the information provided during the order process was not enough to identify the student definitively. For these orders, you should determine the proper ID, add it to the verified ID on SHAETOR and save the record. The system will issue a message indicating the order number has been assigned to that ID and the order will continue to process.

However, there are other reasons the order will have an NR status. Here are some things to check when this happens.

The eTranscript Order detail table (SHRTEOD) contains the order information and this should be reviewed for accuracy.

Ensure the student requested a valid transcript type. For example, did an undergraduate request a graduate transcript?

Check the students academic history - do they have valid courses that would produce a transcript? If not and a blank transcript would be produced, the order will remain in 'NR' status. An 'NR' status will always be added if a blank transcript would result.

If a student requests a transcript and there's no academic history, the status will remain as 'NR'.

'NR' status should be an exception status. If you are getting a high number of these orders, you should review your common matching rules to be sure they are working the way you intend. It may be they are not able to distinguish student IDs from the information being provided. We suggest using complete Last Name, First Name and birthdates in all eTranscripts common matching rules. Often partial names are not enough to identify the student. For additional information, see Article 000031044 - How should SSN and Banner ID 'match on null data' indicators be set up for eTranscripts common matching rules?

Queries

Query to see the orders in Banner:

select * from shbteot

where trunc(shbteot_order_date) = to_date('20200617','yyyymmdd');

Query to see the history of their statuses:

select shrteos.* from shbteot

join shrteos

on shbteot_order_id = shrteos_order_id

where trunc(shbteot_order_date) = to_date('20200617','yyyymmdd') order by shrteos_order_id, shrteos_etst_date;

SQL for the #REG_ETRAN_SHRETRN_PARM01 substitution variable, and the shttran_id

that it's pulling is not a valid GID:

SELECT * FROM

(select shttran_id||shttran_seq_no

FROM saturn.shttran

WHERE (shttran_order_id, shttran_id) IN

(SELECT a.shvteos_order_id, a.shvteos_id

FROM baninst1.shvteos a

WHERE a.shvteos_etst_code = 'RG' AND

a.shvteos_etst_date < (SYSDATE-(1/288)) AND

NOT EXISTS

(SELECT b.*

FROM baninst1.shvteos b

WHERE b.shvteos_order_id = a.shvteos_order_id AND

b.shvteos_etst_code IN ('FF', 'TC', 'GC', 'CA')))

order by shttran_order_id)

where rownum = 1;

Articles

000029457: Overview Steps to Implement eTranscripts in Banner

000042680: Manually process a stuck order

000036530: eTranscripts PDF transcript has a TF status

000034647: eTranscripts orders are stuck in NR status

000031044: How should SSN and Banner ID 'match on null data' indicators be set up for eTranscripts common matching rules?

*reference files are stored on regbackup/eTranscripts

eTranscripts is a way for current and former students to obtain a copy of their University transcripts. This can be reached via links in Sail or MySail. The links should take the user to the National Student Clearinghouse site to place the order.

Source: https://docs.google.com/drawings/d/19fy4-APywQMY0BzOfljRSGDaeEkHMBTcI3ikmTIvaPY/edit?usp=sharing

eTranscript Cloud Diagnostics

You can check connectivity to the Ellucian Cloud Service by going to:

TEST: https://etsapp-stage.10133.elluciancloud.com/
PROD: https://etranscripts.elluciancloud.com/login/auth

Log in as eTranuser@oakland.edu
Click on the Diagnostics link
Enter name and birth date of a known student in the Banner database, and click Test
If all goes well, you should see a Diagnostics Passed message with a green check mark that makes you happy inside all the while oblivious to the darkness that lies beneath

eTranscript Services on the Job Server

Log into the banjobs PROD server: banjobs5.sys.oakland.edu
- TEST server : banjobs6.dev.oakland.edu
Become the banjobs (or bjobtest) user using the command xsu banjobs (or bjobtest)
Run etransps - if the database is up, 10 shradvq processes should be running.
If they are not running and the database is up, run startetrans_inst to start them.
If they need to be stopped, run stopetrans_inst to do so.

SHAETAD configuration

SHAETAD is a configuration page in Banner Admin Pages. Here is how the setting should look.

TEST SHAETAD Configuration

Host Name: stg-ftps.nslc.org
User Name: 00230700-iv_test
SSH Directory: /banjobs/TEST/etrankeys/
Cloud URL: https://etsapp-stage.10133.elluciancloud.com/
Cloud Username: etranuser@oakland.edu

PROD SHAETAD Configuration

Host Name: ftps.nslc.org
User Name: 00230700-iv_prod
SSH Directory: /banjobs/PROD/etrankeys/
Cloud URL: https://etranscripts.elluciancloud.com
Cloud Username: etranuser@oakland.edu

SSH Passphrase and Cloud Username Password

SSH Passphrase: I believe only EA personnel has access to this via the shbtetc table
SQL to decrypt the passphrase: SELECT shkpxex.f_decrypt(shbtetc_ssh_passphrase), shbtetc.* FROM shbtetc;

Cloud Username Password: I believe only EA personnel has access to this via the shbtetc table
SQL to decrypt the passphrase: SELECT shkpxex.f_decrypt(SHBTETC_CLOUD_USER_PASSWORD), shbtetc.* FROM shbtetc;

There also exists an ERP Auth Name: etran provided in the cloud configuration. We have seen a case where that account is locked. To check that: Obtain password from banpass
Attempt a log in to SQL with this user name and password
If locked, then log in as DBA and unlock

Test SFTP connection

You can test the sftp connection using the commands below.

TEST: sftp -v -o 'IdentityFile=/banjobs/TEST/etrankeys/id_rsa' 00230700-iv_test@stg-ftps.nslc.org

PROD: sftp -v -o 'IdentityFile=/banjobs/PROD/etrankeys/id_rsa' 00230700-iv_prod@stg-ftps.nslc.org

UC4

Check that the SHRETRN and SZPFETR jobs are running and not hung.
They are part of the job S_ETRAN_FIX_STUCK_ORDERS. This job runs every 5 minutes. if there are no stuck orders then these two jobs should cancel every five minutes

Tomcat

Per ticket 108635, the TEST server has upgraded to Apache Tomcat 8.5.x on betapi2.dev.oakland.edu.
The following are the new tomcat status/start/stop commands

Status, Stopping and starting tomcat 8.5 can be done with one of the following sets of commands
- sudo /etc/init.d/tomcat85 stop
- sudo /etc/init.d/tomcat85 start
- sudo service tomcat85 status
- sudo service tomcat85 stop
- sudo service tomcat85 start
The Tomcat Webapp directory is now
- /opt/tomcat/tomcat/webapps