Cayuse Sponsored Projects Integration

Standard Operating Procedures

Who to contact for more information

For information regarding the processes  that push data to Cayuse, please contact Joshua Wharton from UTS at joshua.wharton@villanova.edu. 

For information regarding Cayuse Sponsored Projects, please contact Greg Shaffer from the Office of Grants and Contracts at gregory.shaffer@villanova.edu

Who to notify when there is an error in the processes

The system should notify the appropriate individuals, in case of an error, but you are welcome to submit a support ticket in TDX

Who to notify to request changes to the processes

To request a change to this integration, please submit a support ticket in TDX. You will be contacted for more information and a project planned to implement any changes that are deemed needed.

Process Details

CayuseSP HR Connect File Send    

Purpose

This process identifies if a user who is supposed to have access to Cayuse Sponsored Projects (CayuseSP) is new or has returned to the university with an updated username, and then takes the appropriate action based off that. Depending on the need, it generates either a CSV file of user information for new users, and uploads that to the vendor's SFTP server for automated processing, or an email to Villanova team members who can take action on updated records.

Logic Overview

The Banner custom table warehous.fwbcyad, which contains a listing of all usernames and matching pidms for individuals who are a member of the cayuseSPAccess AD group, is queried. Records in this table are considered the authoritative list of who is supposed to have access to CayuseSP. For each record then, it examines the individual to determine if the user fits into one of these three categories:

1. A new individual, who has never had an account for Villanova in CayuseSP before
   For these individuals, key information about them is added to a CSV file. Once all records have been processed and added to the file, the file is uploaded to connect.cayuse424.com, using the username villanova-hrc. An email notification is sent to cayusehrconnect@villanova.edu and joshua.wharton@villanova.edu, with the subject "CayuseSP Run Completion Notification," with a copy of the data that was sent. Additionally, a copy of the CSV file is sent in an email with subject "Output of Cayuse SP Feed" to those addresses, in case manual reprocessing is required. Finally, they are added to the table warehous.fwbcayu, which records all usernames that CayuseSP has.
   
   The following data is sent:
   • Username
   • Cayuse SP Roles (this is a hard-coded list -- for specified users, the value of "sysadmin" is sent)
   • Name prefix
   • First name
   • middle name
   • Last name
   • Name suffix
   • Email address (using the active 2C email type)
   • Phone number (using the 9V telephone type)
   • Title
   • Department
   • Department Code
   • Street address  line1 (using the 9V address type)
   • Street address  line2 (using the 9V address type)
   • City (using the 9V address type)
   • State (using the 9V address type)
   • Zip or postal code (using the 9V address type)
   • Country (using the 9V address type)
   • Villanova ID
   • ERA Role (if the frrgrpi_id_ind is one of '001','002','003','004', or '005', then the role is 'PI')
   • Gender (valid values are M, F, and DO_NOT_WISH_TO_PROVIDE)
   • Ethnicity (valid values are H, for Hispanic, and null)
   • Citizenship (spbpers_citz_code is used; when the value is Y, we pass "US_CITIZEN", when it's N, we pass "OTHER_NON_US_CITIZEN", when it's P, we pass "PERMANENT_RESIDENT", and for all others we pass an empty string)
   • Race: we examine spbpers_ethn_code. These are the values we pass for each of the following codes:
     • A: BLACK_AFRICAN_AMERICAN
     • O: ASIAN
     • H: an empty string
     • W: WHITE
     • I: AMERICAN_INDIAN_ALASKAN_NATIVE
     • X: an empty string
     • All others: an empty string
2. An individual who previously had an account with Villanova in CayuseSP, and has a new username (these are generally individuals who were researchers that left the university, and later came back, after their old username was deleted in Villanova's systems)
   1. We determine if a previous username was used, by examining the custom Banner table warehous.fybcayu. This table contains the pidm and username of every individual who was sent to CayuseSP. If the username for the individual's pidm is the same as what he or she currently has, we set ignore them
   2. Individuals who have different usernames are gathered into an email notification. The old username the user had and the new username are included in this list of all changes.
   3. CayuseSP can not automatically update the username of an individual, so this is a manual operation. The email of all username updates is sent to cayusehrconnect@villanova.edu and joshua.wharton@villanova.edu, with a subject of "Cayuse SP Username Updated Needed" 
3. An individual who has previously had, or currently has, a Villanova account with CayuseSP, and no changes are needed
   These individuals require no updates, so are ignored

At completion, a notification email is sent with subject "CayuseSP Run Completion Notification." If there were no new records, it will indicate that in the body of the email.

Get Cayuse SP AD Group Members

Purpose

This process pulls all the username out of the AD group cayuseSPAccess and updates a custom Banner table with the list. In this way, SQL jobs have access to a list of what usernames are supposed to have access to CayuseSP

Logic Overview

1. A connection is created to the production Villanova AD and the members of CN=cayuseSPAccess,OU=Committees,OU=Groups,DC=vuad,DC=villanova,DC=edu are selected.
2. The contents of the table warehous.fwbcyad are cleared and the usernames in the AD group are populated into this table, along with the username's matching pidm