CRM Recruit Upload

CRM Recruit Upload

About:

The CRM Recruit Upload process is a Job Submission found as a Banner Form in the Application Navigator where it's called "TYPRCRT".

The Job Submission classifies payments made based on the amount and current student status and uploads them into the TBRACCD table in the Banner Database.

Components:

  • CRM Recruit API: The API pulls data from the Ellucian CRM Recruit platform. API access had to be requested from Ellucian (see ticket#CSC03579222). Ellucian provides us with a Client ID and a Client Secret which work together with a username, password, token URL, and a resource URL in order to connect and retrieve data from the API. Those sensitive credentials are encrypted and stored securely on the Job Sub Server. The credentials are decrypted and loaded on-demand into the RAM temporarily when running the Job Submission after which they are cleared.

  • stu-crm Directory: the directory under the path "$DATA_HOME/stu-crm/" is where the users of the Job Sub will upload their CSV extract to be processed by the Job.

  • TYBRCRT Table: The table TYBRCRT is created under the SMLOCAL schema in the database. This table fields like Payment and Detail codes used to classify the data before being uploaded.

  • TYPRCRT Page Builder Page: This page provides the ability to view, edit, add and delete rows from the TYBRCRT table. The purpose of it is so users can maintain the classification codes independently of the EIT team.

  • TYRRCRT Table: the table TYRRCRT is created under the SMLOCAL schema in the database. This table contains all the data that was processed in Update Run Mode. The table was created for the reporting and debugging.

  • SMU_TYPRCRT View: the view SMU_TYPRCRT is created under the BANINST1 schema as a direct view of the TYRRCRT table. It serves the purpose of reporting and debugging.

  • TYPRCRT Job Submission: The Job submission "TYPRCRT" is where the process is invoked from. The process cannot be invoked manually as currently, there are fields that are dependant on the Job Submission invocation parameters. The Job first calls the "typrcrt.shl" shell script which then calls the "typrcrt.sql" PL/SQL script.

  • typrcrt.shl: This script automates the processing of a CSV file containing transaction data. It handles several key steps: decrypting API credentials, normalizing the input CSV (Windows to Unix format), rigorously validating and cleaning the CSV data (checking for mandatory columns, Payer ID length, and sanitizing Amount values), obtaining an OAuth2 access token, querying the CRM API to enrich the data with related entity IDs, and finally generating new CSV files (step1, step2, step3) for further processing by a PL/SQL script. The script also manages logging, temporary file cleanup, and integrates with a Banner Job Submission system for execution and output management, renaming the original input file upon successful completion in update mode.

  • typrcrt.sql: This PL/SQL script processes a CSV file containing transaction data, acting as the backend for the earlier shell script. It retrieves the CSV path, performs extensive validation on the data, and enriches each record by resolving Banner PIDMs, fetching relevant student application data (SARADAP), and dynamically determining appropriate "Charge Code" and "Payment Code" based on the transaction amount and student level. Finally, it generates a new step3 CSV file with the enriched data, inserts the processed records into the TYRRCRT table, and posts the financial transactions to Banner's accounts receivable (TBRACCD) using specific detail codes and PIDMs. The script also maintains a detailed log of its execution.

Process:

1- A user uploads a CSV file (currently extracted from Touchnet) into the path "$DATA_HOME/stu-crm/"

2- A user that has been given access to the "TYPRCRT" Job Submission executes the Job which takes 2 mandatory parameters:

  • File name: which is the CSV file name holding the payment information
  • Run Mode: can be either 'A' (Audit) or 'U' (Update). "Audit" is for testing and does not post any data anywhere. While 'Update' commits the data to the Banner tables.

3- The "TYPRCRT" Job Submission executes a shell script named typrcrt.shl.

4- typrcrt.shl performs the following actions:

  • a. Decrypts API credentials required for CRM interaction.
  • b. Retrieves the CSV file name and the 'Run Mode' parameter passed during job submission.
  • c. Normalizes the input CSV file, converting Windows-style line endings (CRLF) to Unix (LF) and saves it as basename_step1.csv in the same directory.
  • d. Reads and validates each row of basename_step1.csv. This includes checking for the presence of mandatory columns ("Payer ID", "Payer Email", "Amount", "Batch Date"), ensuring correct field counts, validating the 36-character length of "Payer ID", and sanitizing the "Amount" field (removing commas, quotes, and trailing ".00" for zero cents). Only valid rows are kept in memory.
  • e. Obtains an API access token via OAuth2 password grant flow with ADFS.
  • f. Queries the CRM API in batches, using the "Payer ID" to retrieve datatel_relatedentityid values.
  • g. Processes the API responses to build a lookup table and appends the datatel_relatedentityid to each valid CSV row.
  • h. Writes the enriched data to basename_step2.csv in the same directory, also transforming the "Batch Date" to DD-MON-YYYY format.
  • i. Creates an indicator file (csv_step2_filename.txt) containing the full path to basename_step2.csv for the next step.
  • j. Calls a PL/SQL script, typrcrt.sql, using runsqlplus, passing necessary Banner context.

5- The typrcrt.sql PL/SQL script performs these operations:

  • a. Reads the path to basename_step2.csv from the indicator file created by the shell script.
  • b. Validates the CSV file's structure and the presence of expected columns.
  • c. For each data row:
    • i. Maps the datatel_relatedentityid to a PIDM (Banner Person ID) and "A-Number" in Banner's SRBRAID and SPRIDEN tables.
    • ii. Queries for the individual's latest application (SARADAP) record, prioritizing future terms or falling back to a broader search based on the transaction amount.
    • iii. Determines the correct "Charge Code" and "Payment Code" by consulting the TYBRCRT table, based on the transaction amount and the applicant's level (Undergraduate/Graduate).
  • d. Writes a new CSV file, basename_step3.csv, in the same directory, which includes all original and derived data (A-Number, PIDM, SARADAP details, Charge Code, Payment Code).
  • e. Inserts each successfully processed row into the custom Banner table TYRRCRT.
  • f. In 'Update' mode, it posts the financial transactions to Banner's Accounts Receivable (TBRACCD) by calling tb_receivable.p_create twice per row (once for the charge, once for the payment), using a special PIDM for application fees (A00999998) or the student's actual PIDM otherwise.
  • g. Generates a detailed process log, typrcrt_detail_<ONE_UP>.log, in the $DATA_HOME/stu-crm/ directory, providing granular insights into the PL/SQL script's execution.

6- Upon successful completion of the typrcrt.sql script:

  • a. The interim CSV files (_step1.csv, _step2.csv, _step3.csv) are copied to the GURJOBS directory and then removed from their original location in $DATA_HOME/stu-crm/.
  • b. The detailed PL/SQL log file (typrcrt_detail_<ONE_UP>.log) is also copied to GURJOBS and then removed from its original location.
  • c. If the 'Run Mode' was 'U' (Update), the original input CSV file is renamed to have a .processed extension (e.g., input.processed).

7- Finally, the job publishes the main script log (typrcrt_<ONE_UP>.log) and the SQL*Plus output (typrcrt_<ONE_UP>.lis) according to the Banner job submission's print settings (e.g., to the database for viewing in Application Navigator, or to a printer).

 

  

 

Was this helpful?
0 reviews
Print Article