Use an Enrichment function to validate the retrieved data and update a table. For example, to calculate the number of months a case is not Closed and populate this information in the CASES table for each case, whereby for all Closed cases - set the number of month to 0.
String caseStatus = "Closed";
String sqlNotClosed = "UPDATE CASES SET CASE_OPEN_MONTHS = "+
"(SELECT round((julianday('now') - julianday(C2.CASE_DATE))/365*12) from CASES C2 "+
" WHERE C2.CASE_ID = CASES.CASE_ID AND C2.STATUS != ?)";
ludb().execute(sqlNotClosed,caseStatus);
String sqlClosed = "UPDATE CASES SET CASE_OPEN_MONTHS = 0 WHERE STATUS = ?";
ludb().execute(sqlClosed,caseStatus);
Run validation functions to validate LUI data during the Sync process and save the results in a dedicated LU table. Then run an Enrichment function to check the validation results and update a Validation Indicator column in the LU table based on the validation results. The following example describes the validation check if a customer has roaming services and Open cases. It saves the validation results in an LU table and updates CUSTOMER LU table accordingly:
String roamingService = "Roaming special";
String sql = "SELECT count(*) FROM SUBSCRIBER WHERE CONTRACT_DESCRIPTION=? ";
Db.Rows rows = ludb().fetch(sql, roamingService);
Integer hasRoaming = Integer.parseInt(rows.firstValue().toString());
if (hasRoaming > 0){
return "Customer has Roaming services !!";
}
else
return "OK";
Map <String, Map <String,String>> ChecksTrn = getTranslationsData("trnValidationFuncList");
String Func = null;
for(java.util.Map.Entry<String, Map <String,String>> trnVals : ChecksTrn.entrySet()){
Map <String,String> trnVal = (Map <String,String>) trnVals.getValue();
// Checks that the record is active
if(trnVal.get("ACTIVE") != null && rnVal.get("ACTIVE").equalsIgnoreCase("false"))
continue;
String CheckDesc = trnVal.get("CHECK_DESC");
String FuncName = trnVal.get("FUNCTION_NAME");
if(FuncName != null && !FuncName.isEmpty() ){
String Result = Factory.valueOf(FuncName).invoke();
yield(new Object[]{CheckDesc,Result});
}
}
String sqlSelect = "SELECT count(*) FROM EXEC_VALIDATIONS WHERE RESULT !='OK'";
Db.Rows rows = ludb().fetch(sqlSelect);
Integer validationsNotOK = Integer.parseInt(rows.firstValue().toString());
if (validationsNotOK > 0){
String sqlUpdate = "UPDATE CUSTOMER SET VALIDATIONS_NOT_PASSED = ?";
ludb().execute(sqlUpdate, validationsNotOK);
}
A common scenario in Test Data Management (TDM) projects is to add a Parameters LU table to the LU schema, populate it and then aggregate the LU's parameters for business logic and validations. For example, in the Orders LU to get various parameters like the number of MSISDNS or the number of open orders for each Order type, define the list of parameters and the SQL query to be executed on the LU tables for each parameter in a translation object. The Enrichment function then executes the SQL queries for each parameter and populates the Parameters LU table.
Create a new translation named trnOrdersParams that defines a list of parameters and an SQL query for calculating each parameter.
Create a new LU table named ORDERS_PARAMS which is populated by a dummy fnRootORdersParams Root function, the actual population of the table is implemented by the enrichment function.
if (1 == 2) yield(new Object[]{null});
Map<String,Map<String,String>> data = getTranslationsData("trnOrdersParams");
StringBuilder stringInsertFabricLuParam =
new StringBuilder().append("INSERT OR REPLACE INFO ORDERS_PARAMS (IID, ");
String prefix = "";
...
if(data.size() > 0){
// Loop on the translation record
for(String index: data.keySet()) {
...
// Get the SQL query for each parameter
String sql = valMap.get("SQL");
...
//Run the SQL statement on the LU
Db.Rows rows = ludb().fetch(sql);
...
// Build the insert statement by the parameters and their values
String insertSQL = stringInsertFabricLuParam.append(sqlInsertBind).toString();
} // end of loop on the translation values
//Insert a new record to ORDERS_PARAM
ludb().execute(insertSQL, params);
}
Full example of the fnEnrichmentOrderParam function can be found in the Demo project.
Use an Enrichment function to validate the retrieved data and update a table. For example, to calculate the number of months a case is not Closed and populate this information in the CASES table for each case, whereby for all Closed cases - set the number of month to 0.
String caseStatus = "Closed";
String sqlNotClosed = "UPDATE CASES SET CASE_OPEN_MONTHS = "+
"(SELECT round((julianday('now') - julianday(C2.CASE_DATE))/365*12) from CASES C2 "+
" WHERE C2.CASE_ID = CASES.CASE_ID AND C2.STATUS != ?)";
ludb().execute(sqlNotClosed,caseStatus);
String sqlClosed = "UPDATE CASES SET CASE_OPEN_MONTHS = 0 WHERE STATUS = ?";
ludb().execute(sqlClosed,caseStatus);
Run validation functions to validate LUI data during the Sync process and save the results in a dedicated LU table. Then run an Enrichment function to check the validation results and update a Validation Indicator column in the LU table based on the validation results. The following example describes the validation check if a customer has roaming services and Open cases. It saves the validation results in an LU table and updates CUSTOMER LU table accordingly:
String roamingService = "Roaming special";
String sql = "SELECT count(*) FROM SUBSCRIBER WHERE CONTRACT_DESCRIPTION=? ";
Db.Rows rows = ludb().fetch(sql, roamingService);
Integer hasRoaming = Integer.parseInt(rows.firstValue().toString());
if (hasRoaming > 0){
return "Customer has Roaming services !!";
}
else
return "OK";
Map <String, Map <String,String>> ChecksTrn = getTranslationsData("trnValidationFuncList");
String Func = null;
for(java.util.Map.Entry<String, Map <String,String>> trnVals : ChecksTrn.entrySet()){
Map <String,String> trnVal = (Map <String,String>) trnVals.getValue();
// Checks that the record is active
if(trnVal.get("ACTIVE") != null && rnVal.get("ACTIVE").equalsIgnoreCase("false"))
continue;
String CheckDesc = trnVal.get("CHECK_DESC");
String FuncName = trnVal.get("FUNCTION_NAME");
if(FuncName != null && !FuncName.isEmpty() ){
String Result = Factory.valueOf(FuncName).invoke();
yield(new Object[]{CheckDesc,Result});
}
}
String sqlSelect = "SELECT count(*) FROM EXEC_VALIDATIONS WHERE RESULT !='OK'";
Db.Rows rows = ludb().fetch(sqlSelect);
Integer validationsNotOK = Integer.parseInt(rows.firstValue().toString());
if (validationsNotOK > 0){
String sqlUpdate = "UPDATE CUSTOMER SET VALIDATIONS_NOT_PASSED = ?";
ludb().execute(sqlUpdate, validationsNotOK);
}
A common scenario in Test Data Management (TDM) projects is to add a Parameters LU table to the LU schema, populate it and then aggregate the LU's parameters for business logic and validations. For example, in the Orders LU to get various parameters like the number of MSISDNS or the number of open orders for each Order type, define the list of parameters and the SQL query to be executed on the LU tables for each parameter in a translation object. The Enrichment function then executes the SQL queries for each parameter and populates the Parameters LU table.
Create a new translation named trnOrdersParams that defines a list of parameters and an SQL query for calculating each parameter.
Create a new LU table named ORDERS_PARAMS which is populated by a dummy fnRootORdersParams Root function, the actual population of the table is implemented by the enrichment function.
if (1 == 2) yield(new Object[]{null});
Map<String,Map<String,String>> data = getTranslationsData("trnOrdersParams");
StringBuilder stringInsertFabricLuParam =
new StringBuilder().append("INSERT OR REPLACE INFO ORDERS_PARAMS (IID, ");
String prefix = "";
...
if(data.size() > 0){
// Loop on the translation record
for(String index: data.keySet()) {
...
// Get the SQL query for each parameter
String sql = valMap.get("SQL");
...
//Run the SQL statement on the LU
Db.Rows rows = ludb().fetch(sql);
...
// Build the insert statement by the parameters and their values
String insertSQL = stringInsertFabricLuParam.append(sqlInsertBind).toString();
} // end of loop on the translation values
//Insert a new record to ORDERS_PARAM
ludb().execute(insertSQL, params);
}
Full example of the fnEnrichmentOrderParam function can be found in the Demo project.