This solution allows the admin to create rules with conditions that are run against HRSD Cases. When a case meets the conditions of a a rule, an HR Data Quality Issue is generated.
HR Data Quality Rule Table
- Label – HR Data Quality Rule
- Name – sn_hr_core_hr_data_quality_rule
- Extends table: dl_matcher
- Fields
- Name – u_name – string 100
- Table – u_table – table name
- Conditions – u_conditions – Conditions
- Active – u_active – True/False
- How to resolve – u_how_to_resolve – String 1000
- Use script in addition to conditions – u_use_script – True/False
- Script – u_script – Script
HR Data Quality Issue Table (M2M table)
- Label – HR Data Quality Issue
- Name – sn_hr_core_m2m_data_quality_issue
- Fields
- HR Data Quality Rule- u_sn_hr_core_hr_data_quality_rule- Reference sn_hr_core_hr_data_quality_rule
- HR Case – u_sn_hr_core_case – Reference u_sn_hr_core_case
- Status – u_status – Choice (open, resolved, ignored)
UI Action – Run Data Quality Checks
This UI Action runs the scheduled job that performs the checks
Name – Run Data Quality Checks
Table – HR Data Quality Rules
var jobGR = new GlideRecord("sysauto_script");
jobGR.get("[sys_id of scheduled job record]")
gs.executeNow(jobGR);
gs.addInfoMessage('Data Quality Check has been launched');UI Action – Test Data Quality Rule
This UI Action allows the user to test a HR Data Quality Rule without creating HR Data Quality Issue records. It will return the number of cases that the rule picks up.
Name – Test Data Quality Rule
Table – HR Data Quality Rule
action.setRedirectURL(current);
new sn_hr_core.HRSDDataQualityUtils().checkCasesForIssues(null,current.getUniqueValue(),true);UI Action – Check Case for Data Quality Issues
Runs all the HR Data Quality Rules against an HR Case
Name – Check Case for Data Quality Issues
Table – HR Case
action.setRedirectURL(current);
new sn_hr_core.HRSDDataQualityUtils().checkCasesForIssues(current.getUniqueValue());UI Action – Auto-close Resolved Data Quality Issues
Closes HR Data Quality Issues for a case that no longer meet the conditions
Name – Auto-close Resolved Data Quality Issues
Table – HR Case
action.setRedirectURL(current);
new sn_hr_core.HRSDDataQualityUtils().closeIssues(current.getUniqueValue());Script Include
var HRSDDataQualityUtils = Class.create();
HRSDDataQualityUtils.prototype = {
initialize: function() {},
checkCasesForIssues: function(caseSysId, ruleSysId, isTest) {
var testResults = []; //used if isTest is true
var issueFound = false;
var ruleGR = new GlideRecord("sn_hr_core_hr_data_quality_rule");
if (ruleSysId) {
ruleGR.addQuery('sys_id', ruleSysId);
} else {
ruleGR.addQuery("u_active", true);
}
ruleGR.query();
while (ruleGR.next()) {
//look for cases that meet the condition of the rule
var recordGR = new GlideRecord(ruleGR.getValue('u_table'));
recordGR.addEncodedQuery(ruleGR.getValue('u_conditions'));
if (caseSysId) {
recordGR.addQuery('sys_id', caseSysId);
}
recordGR.query();
while (recordGR.next()) {
if (!this.checkIfIssueExists(ruleGR.getUniqueValue(), recordGR.getUniqueValue()) && !isTest) {
this.createIssue(ruleGR.getUniqueValue(), recordGR.getUniqueValue());
issueFound = true;
} else if (isTest && ruleSysId) {
testResults.push(recordGR);
}
}
}
if (caseSysId && issueFound && !isTest) {
gs.addInfoMessage('At least one data quality issue was found, check the Data Quality Issues related list at the bottom of this form')
} else if (caseSysId && !issueFound && !isTest) {
gs.addInfoMessage('No new data quality issues found');
} else if (isTest) {
var examples = '';
var max = 5;
if (testResults.length < 5){
max = testResults.length;
}
for (var i = 0; i < max; i++ ){
examples += testResults[i].getDisplayValue() + ' ';
}
gs.addInfoMessage(testResults.length.toString() + ' cases fit the criteria of this rule. Some examples: ' + examples)
}
},
checkIfIssueExists: function(ruleSysId, caseSysId) {
var issueGR = new GlideRecord('sn_hr_core_m2m_data_quality_issue');
issueGR.addQuery('u_sn_hr_core_case', caseSysId);
issueGR.addQuery('u_sn_hr_core_hr_data_quality_rule', ruleSysId);
issueGR.query();
if (issueGR.next()) {
return true;
} else {
return false;
}
},
createIssue: function(ruleSysId, caseSysId) {
var issueGR = new GlideRecord('sn_hr_core_m2m_data_quality_issue');
issueGR.initialize();
issueGR.setValue('u_sn_hr_core_case', caseSysId);
issueGR.setValue('u_sn_hr_core_hr_data_quality_rule', ruleSysId);
issueGR.insert();
},
runRuleOnCase: function(ruleSysId, caseSysId) {
var ruleGR = new GlideRecord("sn_hr_core_hr_data_quality_rule");
ruleGR.get(ruleSysId);
var caseGR = new GlideRecord(ruleGR.getValue('u_table'));
caseGR.addEncodedQuery(ruleGR.getValue('u_conditions'));
caseGR.addQuery('sys_id', caseSysId);
caseGR.query();
if (caseGR.getRowCount() > 0) {
//since the conditions evaluated to true above, check if there is something in the script field
if (ruleGR.u_use_script) {
return runRuleScriptOnCase(ruleGR, caseGR);
} else {
//if there isn't any script, return true;
return true;
}
} else {
//if 0 rows were returned, the condition is not met, return false
return false;
}
},
runRuleScriptOnCase: function(ruleGR, caseGR) {
//set the inputVars object that will be passed into GlideScopedEvaluator
var inputVars = {
"current": caseGR,
"answer": false
};
var ge = new GlideScopedEvaluator();
ge.evaluateScript(ruleGR, 'u_script', inputVars);
//pass the answer variable back.
return ge.getVariable('answer');
},
closeIssues: function(caseSysId) {
var issueGR = new GlideRecord('sn_hr_core_m2m_data_quality_issue');
issueGR.addQuery('u_status', 'open');
if (caseSysId) {
issueGR.addQuery('u_sn_hr_core_case', caseSysId);
}
issueGR.query();
while (issueGR.next()) {
if (!this.runRuleOnCase(issueGR.getValue('u_sn_hr_core_hr_data_quality_rule'), issueGR.getValue('u_sn_hr_core_case'))) {
issueGR.setValue('u_status', 'resolved');
var comments = 'Auto closed by system on ' + new GlideDateTime() + ' UTC\n' + '----------------\n';
if (issueGR.getValue('u_comments')) {
comments += '\n' + issueGR.getValue('u_comments')
}
issueGR.setValue('u_comments', comments);
issueGR.update();
if (caseSysId) {
gs.addInfoMessage('Data Quality Issue "' + issueGR.u_sn_hr_core_hr_data_quality_rule.u_name.getDisplayValue() + '" set to Resolved');
}
}
}
},
getClassificationCasesWithoutDraftApprovalTasks: function() {
result = [];
//finds Classification Cases without PD Draft Approval tasks
var listGR = new GlideRecord("sn_hr_core_case_talent_management");
listGR.addEncodedQuery("active=false^hr_service=8918c56f87136510ea4fffb2debb352e");
listGR.query();
while (listGR.next()) {
//check for Draft Approval task
var taskGR = new GlideRecord('sn_hr_core_task');
taskGR.addQuery('parent', listGR.getUniqueValue());
taskGR.addQuery('employee_form', 'bd2eda551b843590ae5e1066b04bcbd6'); //draft approval
taskGR.query();
if (taskGR.getRowCount() == 0) {
result.push(listGR.getUniqueValue());
}
}
return result.toString();
},
type: 'HRSDDataQualityUtils'
};