Match Student

This document outlines the workflow for matching student records from Slate to existing records in Banner. The process uses both precise and fuzzy matching techniques to ensure accurate and efficient integration of new student records.

Processing Workflow

Initialize Variables (initialize_variables)
- Standardizes data fields (e.g., names, addresses) for consistency.
- Prepares the record for matching by initializing key variables.
Run Matching Functions
- Precise Matching: Uses exact criteria such as PIDM, SSN, and Slate Reference ID to find records.
- Fuzzy Matching: Applies lenient criteria (e.g., partial names, DOB, address) to find approximate matches.
Execute Fuzzy Queries (execute_fuzzies)
- Runs accumulated fuzzy queries to retrieve potential matches from the database.
Finalize Variables (finalize_variables)
- Evaluates matching results and determines the final action for the record:
  - Add: Insert new record if no match is found.
  - Update: Update existing record if a single match is confirmed.
  - Suspend: Flag record for review if multiple matches are found.

Flowchart

Matching Functions Overview

Function Name	Criteria	Type	Purpose
`initialize_variables_match`	N/A	Setup	Prepares variables for matching.
`match_pidm`	PIDM	Precise	Matches by PIDM.
`match_banner_id`	Banner ID	Precise	Matches by Banner ID.
`match_ref_id`	Slate Ref ID and Type	Precise	Matches by Slate Ref ID.
`match_ssn_last`	SSN and Last Name	Precise	Matches by SSN and last name.
`match_last_dob_ssn4`	Last Name, DOB, Last 4 Digits of SSN	Precise	Matches by last name, DOB, and SSN4.
`match_exact_last_first_dob_street1`	Last Name, First Name, DOB, Address Street1	Precise	Matches by name, DOB, and address.
`match_exact_last_first_dob`	Last Name, First Name, DOB	Precise	Matches by name and DOB.
`match_ssn`	SSN	Precise	Matches by SSN.
`match_last5_first2_dob`	First 5 of Last Name, First 2 of First Name, DOB	Fuzzy	Matches partial names and DOB.
`match_last_dob`	Last Name (partial) and DOB	Fuzzy	Matches partial last name and DOB.
`match_last5_dob`	First 5 of Last Name and DOB	Fuzzy	Matches partial last name and DOB.
`match_end_last_first1_dob`	Last Characters of Last Name, First Initial, DOB	Fuzzy	Matches by partial last and first names.
`match_last_first3_eq_mi3`	First 3 of First Name, Last Name, MI	Fuzzy	Matches by names and middle initial.
`match_last5_first3_city`	First 5 of Last Name, First 3 of First Name, City	Fuzzy	Matches by name and city.
`match_last_eq_first`	Swapped First and Last Names	Fuzzy	Matches swapped names.
`execute_fuzzies`	N/A	Execution	Runs accumulated fuzzy queries.
`finalize_variables`	N/A	Finalize	Determines final status of the record.

Setup Functions

`initialize_variables_match`

Purpose: Prepares and standardizes the student record for the matching process by initializing key variables and standardizing names and addresses.

void initialize_variables_match() {
	
	svc_banner_read = "read_bind";

	//Put any variable adjustments you need here
	_record.put("match_status", "matched"); //status is matched unless we set it to new
	_record.put("match_first_name", toUpperCase(replaceAll(_record.get("student_first_name"), "[' -]", "")));
	_record.put("match_last_name", toUpperCase(replaceAll(_record.get("student_last_name"), "[' -]", "")));
	
	//Address stuff
	_record.put("match_address_perm_street1", toUpperCase(replaceAll(_record.get("perm_address_street1"), "'", "")));
	_record.put("match_address_perm_city", toUpperCase(replaceAll(_record.get("perm_address_city"), "'", "")));
	_record.put("match_address_perm_type", toUpperCase(replaceAll(_record.get("perm_address_type"), "'", "")));
	
	_record.put("match_address_mail_street1", toUpperCase(replaceAll(_record.get("mail_address_street1"), "'", "")));
	_record.put("match_address_mail_city", toUpperCase(replaceAll(_record.get("mail_address_city"), "'", "")));
	_record.put("match_address_mail_type", toUpperCase(replaceAll(_record.get("mail_address_type"), "'", "")));		
	
	match_definite = false; //Only the first few match statuses can set match_definite to true
	match_count = 0;	
	
}

Matching Functions

`match_pidm`

Purpose: Matches records using the PIDM (Personal Identifier in Banner). This is a precise match, typically used when a PIDM is already known.

void match_pidm() {

	string rev = Batch.getRevision();
	rev = toUpperCase(rev);

	if (rev == "PRODUCTION") {

		string banner_pidm = _record.get("banner_pidm");

		if (banner_pidm == "" || banner_pidm == " " || banner_pidm == null) {
			Record.log("PIDM blank so continuing search...");
			match_definite = false;
		} else {
			rank = "1";

			string select_statement =
				"SELECT distinct spriden_pidm pidm, '" + rank + "' rank " +
				"FROM spriden " +
				"WHERE spriden_pidm = ? " +
				"  AND spriden_change_ind is null ";

			string[] parameters = {banner_pidm};	

			DBUtilities.log_sql("match_banner_pidm - " + select_statement, log_sql);

			//The the lines below is more or less boiler plate. However, be sure to read the comments
			Map[] ids = DBUtilities.read_bind(sg_banner, svc_banner_read, select_statement, parameters);

			//The two instances of the word "id" need to match with what you're selecting in the select statement
			matches = MatchUtilities.add_matches_to_array(matches, ids, {"PIDM", "RANK"}, "PIDM");
			match_count = toInteger(matches.size() / 2);

			if(match_count < 1) {
				// this will only occur if PIDM is part of the data being processed from an extract, but the pidm is not in Banner
				Record.log("PIDM could not be found...");
				Record.error("Constituo was sent a PIDM that doesn't exist in Banner");
			}

			if(match_count > 1) {
				// something has gone really really wrong if this is the case
				Record.setMessage("Possible duplicate Banner PIDM...");
				Record.log("PIDM is duplicated in Banner.  Please investigate");
				match_definite = false;
			} else if (match_count == 1) {			
				match_definite = true;
			}		
		}
	} else {
		Record.log("Skipping PIDM match since we are in Test mode.");
	}
}

`match_banner_id()`

Purpose: Matches records based on the Banner ID. This is a precise match, leveraging the unique Banner system identifier.

void match_banner_id() {

	string alt_id = _record.get("banner_id");
	alt_id = alt_id == null ? "" : alt_id;

	if(alt_id != "") {
		rank = "1";

		string select_statement =
			"SELECT distinct spriden_pidm pidm, '" + rank + "' rank " +
			"FROM spriden " +
			"WHERE spriden_id = ? " +
			"  AND spriden_change_ind is null ";

		string[] parameters = {alt_id};	

		DBUtilities.log_sql("match_alt_id - " + select_statement, log_sql);

		//The the lines below is more or less boiler plate. However, be sure to read the comments
		Map[] ids = DBUtilities.read_bind(sg_banner, svc_banner_read, select_statement, parameters);

		//The two instances of the word "id" need to match with what you're selecting in the select statement
		matches = MatchUtilities.add_matches_to_array(matches, ids, {"PIDM", "RANK"}, "PIDM");
		match_count = toInteger(matches.size() / 2);		

		if(match_count > 1) {
			Record.setMessage("Possible duplicate Slate App ID...");
			match_definite = false;
		} else if (match_count == 1) {			
			match_definite = true;
		}		
	}		
}

`match_ref_id()`

Purpose: Matches records using the Slate Reference ID and its type. Ensures Slate ID consistency with Banner records.

void match_ref_id() {

	string alt_id = _record.get("slate_ref");
	string alt_id_type = _record.get("slate_ref_type");
	
	if(alt_id != "") {

		rank = "1";

		string select_statement =
			"SELECT distinct goradid_pidm pidm, '" + rank + "' rank " +
			"  FROM goradid " +
			" WHERE goradid_additional_id = ? " +
			"   AND goradid_adid_code = ? ";
		
		string[] parameters = {alt_id, alt_id_type};

		DBUtilities.log_sql("match_ref_id - " + select_statement, log_sql);

		//The the lines below is more or less boiler plate. However, be sure to read the comments
		Map[] ids = DBUtilities.read_bind(sg_banner, svc_banner_read, select_statement, parameters);
		//ids = Utilities.map_array_add_key(ids, "rank", rank);

		//The two instances of the word "id" need to match with what you're selecting in the select statement
		matches = MatchUtilities.add_matches_to_array(matches, ids, {"PIDM", "RANK"}, "PIDM");
		match_count = toInteger(matches.size() / 2);		

		if(match_count > 1) {
			Record.setMessage("Possible duplicate Ref ID...");
			match_definite = false;
		} else if (match_count == 1) {
			match_definite = true;
		}		
	}
}

`match_ssn_last()`

Purpose: Matches records by Social Security Number (SSN) and last name. Provides a strong precise match based on unique personal identifiers.

void match_ssn_last() {

	string ssn = _record.get("ssn");
	string last_name = _record.get("match_last_name");

	if(ssn != "" && last_name != "") {
		rank = "1";

		string select_statement =
			"SELECT spbpers_pidm pidm, '" + rank + "' rank " +
			"FROM spriden " +
			"JOIN spbpers on spriden_pidm = spbpers_pidm " +
			"WHERE spbpers_ssn = ? " +
			"AND spriden_search_last_name = ? ";

		string[] parameters = {ssn, last_name};

		DBUtilities.log_sql("match_ssn_last - " + select_statement, log_sql);

		//The the lines below is more or less boiler plate. However, be sure to read the comments
		Map[] ids = DBUtilities.read_bind(sg_banner, svc_banner_read, select_statement, parameters);

		//The two instances of the word "id" need to match with what you're selecting in the select statement
		matches = MatchUtilities.add_matches_to_array(matches, ids, {"PIDM", "RANK"}, "PIDM");
		match_count = toInteger(matches.size() / 2);		

		if(match_count > 1) {
			Record.wait("Possible duplicate SSN...");
		} else if (match_count == 1) {
			match_definite = true;
		}		
	}	
}

`match_last_dob_ssn4()`

Purpose: Matches records based on the last name, date of birth (DOB), and the last 4 digits of the SSN. Offers a more specific match while using partial SSN data.

void match_last_dob_ssn4() {
	
	string last_name = _record.get("match_last_name") + "%";
	string birth_date = toUpperCase(_record.get("birth_date"));
	string ssn = _record.get("ssn");

	if(birth_date != "" && ssn != "") {	
		
		rank = "01";

		ssn = substr(ssn, 5, 9);
		string where_last_name = "upper(spriden_search_last_name) like ?";	

		string select_statement =
			"SELECT distinct spbpers_pidm pidm, '" + rank + "' rank " +
			"FROM spriden " + 
			"JOIN spbpers on spriden_pidm = spbpers_pidm " +		
			"WHERE " + where_last_name +
			"AND spriden_change_ind IS NULL " +
			"AND spriden_entity_ind = ? " +
			"AND to_char(spbpers_birth_date, 'DD-MON-YYYY') = ? " +
			"AND substr(spbpers_ssn,6,4) = ? ";

		DBUtilities.log_sql("match_last_dob_ssn4 - " + select_statement, log_sql);
		string[] parameters = {last_name, "P", birth_date, ssn};

		//The the lines below is more or less boiler plate. However, be sure to read the comments
		Map[] ids = DBUtilities.read_bind(sg_banner, svc_banner_read, select_statement, parameters);

		//The two instances of the word "PIDM" need to match with what you're selecting in the select statement (but upper case because Oracle)
		matches = MatchUtilities.add_matches_to_array(matches, ids, {"PIDM", "RANK"}, "PIDM");
		match_count = toInteger(matches.size() / 2);		

		if(match_count > 1) {
			Record.setMessage("Possible duplicate Last/DOB/SSN...");
			match_definite = false;
		} else if (match_count == 1) {
			match_definite = true;
		}
	}
}

`match_exact_last_first_dob_street1()`

Purpose: Matches records using an exact match on last name, first name, DOB, and address street1. This ensures a highly accurate match by leveraging multiple data points.

void match_exact_last_first_dob_street1() {

	rank = "01";

	string street1_mail = toUpperCase(_record.get("match_address_perm_street1")) + "%";
	string street1_perm = toUpperCase(_record.get("match_address_mail_street1")) + "%";

	string mail_type = _record.get("match_address_perm_type");
	string perm_type = _record.get("match_address_mail_type");

	string first_name = _record.get("match_first_name");
	string last_name = _record.get("match_last_name");

	if(!(street1_mail == "" && street1_perm == "")) {

		string where_street1 = 
			"(upper(mail.spraddr_street_line1) like ? or " + 	//street1_mail
			" upper(mail.spraddr_street_line1) like ? or " + 	//street1_perm
			" upper(perm.spraddr_street_line1) like ? or " + 	//street1_mail
			" upper(perm.spraddr_street_line1) like ?)";		//street1_perm

		string[] where_first_name_params = MatchUtilities.where_lookup_parameters("First Names", first_name, "", "");
		string where_first_name_body = MatchUtilities.where_lookup_body("spriden_search_first_name", where_first_name_params, "", "");		
		
		string where_last_name = "upper(spriden_search_last_name) = ? ";		 //last_name

		string birth_date = toUpperCase(_record.get("birth_date"));

		string select_statement =
			"SELECT distinct spbpers_pidm pidm, '" + rank + "' rank " +
			"FROM spriden " + 
			"JOIN spbpers on spriden_pidm = spbpers_pidm " +	
			"LEFT JOIN spraddr mail on spriden_pidm = mail.spraddr_pidm and mail.spraddr_atyp_code = '" + mail_type + "' " +
			"LEFT JOIN spraddr perm on spriden_pidm = perm.spraddr_pidm and perm.spraddr_atyp_code = '" + perm_type + "' " +
			"WHERE " + where_first_name_body +
			"  AND " + where_last_name + 
			"  AND " + where_street1 +
			"  AND spriden_change_ind IS NULL " +
			"  AND spriden_entity_ind = ? " +
			"  AND to_char(spbpers_birth_date, 'DD-MON-YYYY') = ? ";

		string[] parameters = where_first_name_params;
		parameters.add(last_name);	
		parameters.add(street1_mail);
		parameters.add(street1_perm);
		parameters.add(street1_mail);
		parameters.add(street1_perm);
		parameters.add("P");
		parameters.add(birth_date);

		DBUtilities.log_sql("match_exact_last_first_dob_street1 - " + select_statement, log_sql);

		//The the lines below is more or less boiler plate. However, be sure to read the comments
		Map[] ids = DBUtilities.read_bind(sg_banner, svc_banner_read, select_statement, parameters);

		//The two instances of the word "PIDM" need to match with what you're selecting in the select statement (but upper case because Oracle)
		matches = MatchUtilities.add_matches_to_array(matches, ids, {"PIDM", "RANK"}, "PIDM");
		match_count = toInteger(matches.size() / 2);	

		if(match_count > 1) {
			Record.setMessage("Possible duplicate Last/First/DOB/Street1...");
			match_definite = false;
		} else if (match_count == 1) {
			match_definite = true;
		}			

	}

}

`match_exact_last_first_dob()`

Purpose: Matches records using an exact match on last name, first name, and DOB. This is a precise match focusing on critical personal information.

void match_exact_last_first_dob() {
    rank = "01";

    string first_name = _record.get("match_first_name");
    string last_name = _record.get("match_last_name");
    string birth_date = toUpperCase(_record.get("birth_date"));

    if (birth_date != "") {
        string[] where_first_name_params = MatchUtilities.where_lookup_parameters("First Names", first_name, "", "");
        string where_first_name_body = MatchUtilities.where_lookup_body("spriden_search_first_name", where_first_name_params, "", "");

        string where_last_name = "upper(spriden_search_last_name) = ?";

        string select_statement =
            "SELECT distinct spbpers_pidm pidm, '" + rank + "' rank " +
            "FROM spriden " +
            "JOIN spbpers ON spriden_pidm = spbpers_pidm " +
            "WHERE " + where_first_name_body +
            " AND " + where_last_name +
            " AND spriden_change_ind IS NULL " +
            " AND spriden_entity_ind = ? " +
            " AND to_char(spbpers_birth_date, 'DD-MON-YYYY') = ?";

        string[] parameters = where_first_name_params;
        parameters.add(last_name);
        parameters.add("P");
        parameters.add(birth_date);

        DBUtilities.log_sql("match_exact_last_first_dob - " + select_statement, log_sql);

        fuzzy_queries = fuzzy_queries == null ? select_statement : fuzzy_queries + " UNION " + select_statement;
        fuzzy_parameters = STRS.concat(fuzzy_parameters, parameters, true);
    }
}

`match_ssn()`

Purpose: Matches records using the Social Security Number (SSN) as a unique identifier. This is one of the most reliable precise matching methods.

void match_ssn() {
    string ssn = _record.get("ssn");

    if (ssn != "") {
        rank = "2";

        string select_statement =
            "SELECT spbpers_pidm pidm, '" + rank + "' rank " +
            "FROM spbpers " +
            "WHERE spbpers_ssn = ?";

        string[] parameters = {ssn};

        DBUtilities.log_sql("match_ssn - " + select_statement, log_sql);

        fuzzy_queries = fuzzy_queries == null ? select_statement : fuzzy_queries + " UNION " + select_statement;
        fuzzy_parameters = STRS.concat(fuzzy_parameters, parameters, true);
    }
}

Fuzzy Matching Functions

`match_last5_first2_dob()`

Purpose: Matches records using the first 5 characters of the last name, first 2 characters of the first name, and DOB. Offers flexibility for name variations.

void match_last5_first2_dob() {

	rank = "02";

	string birth_date = toUpperCase(_record.get("birth_date"));
	string first_name = _record.get("match_first_name");
	string last_name5 = substr(_record.get("match_last_name"), 0, 5) + "%";

	string where_last_name = "upper(spriden_search_last_name) like ? ";
	
	string[] where_first_name_params = MatchUtilities.where_lookup_parameters("First Names", first_name, "", "");
	string where_first_name_body = MatchUtilities.where_lookup_body("substr(spriden_search_first_name, 1, 2)", where_first_name_params, "", "");

	if(birth_date != "") {
		string select_statement =
			"SELECT spbpers_pidm pidm, '" + rank + "' rank " +
			"FROM spriden " + 
			"JOIN spbpers on spriden_pidm = spbpers_pidm " +		
			"WHERE " + where_first_name_body +
			"AND " +  where_last_name +
			"AND spriden_change_ind IS NULL " +
			"AND spriden_entity_ind = ? " +
			"AND to_char(spbpers_birth_date, 'DD-MON-YYYY') = ? " +
			"AND spbpers_dead_ind is null ";	

		string[] parameters = where_first_name_params;
		parameters.add(last_name5);
		parameters.add("P");
		parameters.add(birth_date);
		
		DBUtilities.log_sql("match_last5_first2_dob - " + select_statement, log_sql);
		
		fuzzy_queries = fuzzy_queries == null ? select_statement : fuzzy_queries + " UNION " + select_statement;
		fuzzy_parameters = STRS.concat(fuzzy_parameters, parameters, true);		

	}
}

`match_last_dob()`

Purpose: Matches records by partial last name and DOB. Useful when only a portion of the last name is available or consistent.

void match_last_dob() {

	rank = "03";

	string birth_date = toUpperCase(_record.get("birth_date"));
	string last_name = _record.get("match_last_name") + "%";

	string where_last_name = "upper(spriden_search_last_name) like ? ";		

	if(birth_date != "") {	
		string select_statement =
			"SELECT spbpers_pidm pidm, '" + rank + "' rank " +
			"FROM spriden " + 
			"JOIN spbpers on spriden_pidm = spbpers_pidm " +		
			"WHERE " + where_last_name +
			"AND spriden_change_ind IS NULL " +
			"AND spriden_entity_ind = ? " +
			"AND to_char(spbpers_birth_date, 'DD-MON-YYYY') = ? ";

		string[] parameters = {last_name, "P", birth_date};
		DBUtilities.log_sql("match_last_dob - " + select_statement, log_sql);

		fuzzy_queries = fuzzy_queries == null ? select_statement : fuzzy_queries + " UNION " + select_statement;
		fuzzy_parameters = STRS.concat(fuzzy_parameters, parameters, true);
	}
}

`match_last5_dob()`

Purpose: Matches using the first 5 characters of the last name and DOB. A flexible approach for handling name variations.

void match_last5_dob() {

	rank = "04";
	string last_name = _record.get("match_last_name");
	string last_name5 = substr(last_name, 0, 5) + "%";
	string where_last_name = "upper(spriden_search_last_name) like ? ";

	string birth_date = toUpperCase(_record.get("birth_date"));

	if(birth_date != "") {
		string select_statement =
			"SELECT spbpers_pidm pidm, '" + rank + "' rank " +
			"FROM spriden " + 
			"JOIN spbpers on spriden_pidm = spbpers_pidm " +		
			"WHERE " + where_last_name +
			"AND spriden_change_ind IS NULL " +
			"AND spriden_entity_ind = ? " +
			"AND to_char(spbpers_birth_date, 'DD-MON-YYYY') = ? " +
			"AND spbpers_dead_ind is null ";

		string[] parameters = {last_name5, "P", birth_date};
		DBUtilities.log_sql("match_last5_dob - " + select_statement, log_sql);

		fuzzy_queries = fuzzy_queries == null ? select_statement : fuzzy_queries + " UNION " + select_statement;
		fuzzy_parameters = STRS.concat(fuzzy_parameters, parameters, true);
	}
}

`match_end_last_first1_dob()`

Purpose: Matches records based on the last characters of the last name, first character of the first name, and DOB. Useful for identifying records with suffix variations or typos.

void match_end_last_first1_dob() {
    rank = "06";

    string last_name = _record.get("match_last_name");
    string first_name = _record.get("match_first_name");
    string birth_date = toUpperCase(_record.get("birth_date"));

    string last_name6;
    if (length(last_name) >= 6) {
        last_name6 = "%" + Utilities.substr_safe(last_name, 4, length(last_name));
    } else {
        last_name6 = "%" + last_name + "%";
    }

    if (birth_date != "") {
        string where_last_name = "upper(spriden_search_last_name) like ?";

        string[] where_first_name_params = MatchUtilities.where_lookup_parameters("First Names", first_name, "", "");
        string where_first_name_body = MatchUtilities.where_lookup_body("substr(spriden_search_first_name, 1, 1)", where_first_name_params, "", "");

        string select_statement =
            "SELECT spbpers_pidm pidm, '" + rank + "' rank " +
            "FROM spriden " +
            "JOIN spbpers ON spriden_pidm = spbpers_pidm " +
            "WHERE " + where_first_name_body +
            " AND " + where_last_name +
            " AND spriden_change_ind IS NULL " +
            " AND spriden_entity_ind = ? " +
            " AND to_char(spbpers_birth_date, 'DD-MON-YYYY') = ? " +
            " AND spbpers_dead_ind IS NULL";

        string[] parameters = where_first_name_params;
        parameters.add(last_name6);
        parameters.add("P");
        parameters.add(birth_date);

        DBUtilities.log_sql("match_end_last_first1_dob - " + select_statement, log_sql);

        fuzzy_queries = fuzzy_queries == null ? select_statement : fuzzy_queries + " UNION " + select_statement;
        fuzzy_parameters = STRS.concat(fuzzy_parameters, parameters, true);
    }
}

`match_last_first3_eq_mi3()`

Purpose: Matches records using the first 3 characters of the first name, last name, and the first 3 characters of the middle initial (MI). Allows partial name matching for accuracy.

void match_last_first3_eq_mi3() {
    rank = "07";

    string last_name = _record.get("match_last_name") + "%"; // Partial match for last name
    string first_name = _record.get("match_first_name");
    string birth_date = _record.get("birth_date");

    if (birth_date != "") {
        string where_last_name = "upper(spriden_search_last_name) like ?";

        string[] where_first_name_params = MatchUtilities.where_lookup_parameters("First Names", first_name, "", "");
        string where_first_name_body = MatchUtilities.where_lookup_body("substr(spriden_search_mi, 1, 3)", where_first_name_params, "", "");

        string select_statement =
            "SELECT spbpers_pidm pidm, '" + rank + "' rank " +
            "FROM spriden " +
            "JOIN spbpers ON spriden_pidm = spbpers_pidm " +
            "WHERE " + where_first_name_body +
            " AND " + where_last_name +
            " AND spriden_change_ind IS NULL " +
            " AND spriden_entity_ind = ? " +
            " AND to_char(spbpers_birth_date, 'DD-MON-YYYY') = ? " +
            " AND spbpers_dead_ind IS NULL";

        string[] parameters = where_first_name_params;
        parameters.add(last_name);
        parameters.add("P");
        parameters.add(birth_date);

        DBUtilities.log_sql("match_last_first3_eq_mi3 - " + select_statement, log_sql);

        fuzzy_queries = fuzzy_queries == null ? select_statement : fuzzy_queries + " UNION " + select_statement;
        fuzzy_parameters = STRS.concat(fuzzy_parameters, parameters, true);
    }
}

`match_last5_first3_city()`

Purpose: Matches records using the first 5 characters of the last name, first 3 characters of the first name, and the city from the permanent address. Incorporates both name and location data.

void match_last5_first3_city() {
    rank = "08";

    string last_name5 = substr(_record.get("match_last_name"), 0, 5) + "%"; // First 5 characters of last name
    string where_last_name = "upper(spriden_search_last_name) like ?";

    string first_name = _record.get("match_first_name");
    string[] where_first_name_params = MatchUtilities.where_lookup_parameters("First Names", first_name, "", "");
    string where_first_name_body = MatchUtilities.where_lookup_body("substr(spriden_search_first_name, 1, 3)", where_first_name_params, "", "");

    string city = toUpperCase(_record.get("perm_address_city"));
    string city_where = "upper(spraddr_city) = ?";

    string select_statement =
        "SELECT spbpers_pidm pidm, '" + rank + "' rank " +
        "FROM spriden " +
        "JOIN spbpers ON spriden_pidm = spbpers_pidm " +
        "JOIN spraddr ON spriden_pidm = spraddr_pidm " +
        "WHERE " + where_first_name_body +
        " AND " + where_last_name +
        " AND spriden_change_ind IS NULL " +
        " AND spriden_entity_ind = ? " +
        " AND " + city_where +
        " AND spbpers_dead_ind IS NULL";

    string[] parameters = where_first_name_params;
    parameters.add(last_name5);
    parameters.add("P");
    parameters.add(city);

    DBUtilities.log_sql("match_last5_first3_city - " + select_statement, log_sql);

    fuzzy_queries = fuzzy_queries == null ? select_statement : fuzzy_queries + " UNION " + select_statement;
    fuzzy_parameters = STRS.concat(fuzzy_parameters, parameters, true);
}

`match_last_eq_first()`

Purpose: Matches records where the first name matches the last name and vice versa. Addresses potential data entry errors where names are swapped.

void match_last_eq_first() {

	rank = "09";

	string first_name = _record.get("match_first_name");
	string last_name = substr(_record.get("match_last_name"), 0, 5) + "%";
	string where_last_name = "upper(spriden_search_first_name) like ? ";	

	string[] where_first_name_params = MatchUtilities.where_lookup_parameters("First Names", first_name, "", "");
	string where_first_name_body = MatchUtilities.where_lookup_body("spriden_search_last_name", where_first_name_params, "", "");	

	string select_statement =
		"SELECT spriden_pidm pidm, '" + rank + "' rank " +
		"FROM spriden " + 	
		"WHERE " + where_first_name_body +
		"AND " + where_last_name + 
		"AND spriden_change_ind IS NULL " +
		"AND spriden_entity_ind = ? ";

	string[] parameters = where_first_name_params;
	parameters.add(last_name);
	parameters.add("P");	

	DBUtilities.log_sql("match_last_eq_first - " + select_statement, log_sql);
	
	fuzzy_queries = fuzzy_queries == null ? select_statement : fuzzy_queries + " UNION " + select_statement;
	fuzzy_parameters = STRS.concat(fuzzy_parameters, parameters, true);		
}

Finalization Functions

`execute_fuzzies()`

Purpose: Executes all accumulated fuzzy matching queries to retrieve potential matches.

void execute_fuzzies() {
	
	Map[] ids = DBUtilities.read_bind(sg_banner, svc_banner_read, fuzzy_queries, fuzzy_parameters);

	matches = MatchUtilities.add_matches_to_array(matches, ids, {"PIDM", "RANK"}, "PIDM");
	match_count = toInteger(matches.size() / 2);		

}

`finalize_variables()`

Purpose: Finalizes the match process by evaluating match results and determining the next steps: add a new record, update an existing record, or suspend the record for manual review.

void finalize_variables() {

	//Set to true to force user remediation form to appear with specific pidms (bottom)
	boolean testing = false;
	
// 	also for testing
// 	match_definite = false;
	
	match_count = toInteger(matches.size() / 2);	
		
	if (match_count == 0) {
		Record.log("No matches found - adding");
		Record.setMessage("Adding to Banner");
// 	} else if (match_count == 1 ) {
	} else if (match_count == 1 && match_definite == true) {
		_record.put("pidm", toString(matches[0]));
		Record.log("One match found, " + matches[0] + ", updating Banner");
		Record.setMessage("Updating Banner");
	} else {
		
		string message = match_count == 1 ? " match found. Suspending record." : " matches found. Suspending record.";
		
		Record.log(match_count + message);
		Record.setMessage(match_count + message);
	}
	
	/* 
	 * For testing: If we don't have any matches, let's just make a few
	 */	
	if(testing) {
		matches = {
			"255005", "1", //put in the pidm first and then the rank
			"255003", "2",
			"255002", "3"
		};
	
		match_count = 2;
	}
	
	svc_banner_read = "read";
}

Last modified: 13 January 2025