Routing Query

The routing query is implemented via an IVR object with the following properties.

jtel Portal WIKI > Nexus RIS Integration > Nexus RIS Query 2.PNG

Function

The functionality of the object is as follows:

The system uses the settings of the routing application to determine whether it is a test call.
- For test calls, the test data source is used for the query. For production calls, the production data source is used.
  Note: A test call in the IVR is initiated with the setting "Test call number" in the master data of the routing application.
The object implementation then calls the function EXEC pkg_rtma.set_mandant(x) to determine the client with the tenant ID set in the object.
Finally, the specified query is executed. The query can contain any variables, for example $caller.

Requirements

Installed and configured Oracle Instant Client on the jtel server, for production (minimum) and test database (optional) See also Oracle ODBC Driver.

System parameters

The following system parameters influence the operation of the object:

Parameter

Meaning

Nexus.RIS.DSN.Production

Name of the ODBC data source, for production calls.

Nexus.RIS.DSN.Production.Connect

ODBC connection string for the connection to the production database. Example:

DSN=nexus_prod;UID=prod_user;PWD=topsecret

Nexus.RIS.DSN.Test

Name of the ODBC data source, for test calls.

Nexus.RIS.DSN.Test.Connect

ODBC connection string for the connection to the test database. Example:

DSN=nexus_prod;UID=prod_user;PWD=topsecret

Parameters

Parameter

Meaning

Tenant ID

The ID of the client that is used for the parameter x (client ID) for the call to EXEC pkg_rtma.set_mandant(x). This call is made transparently in the object itself.

Variable Prefix

A prefix that precedes all variables from the query return. For example, if the query returns a field "ISVIP" and "nexus" is used as a prefix, the object declares a variable named nexus.ISVIP

Query

The query to be executed. For example:

SELECT COUNT(*) AS RecordCount, MIN(pers.pers_kz_vip) AS isVIP 
FROM person pers, pat_kontakt kont 
WHERE pers.piz = kont.kont_piz AND kont_nummer = '$caller';

Variables

All fields returned from the query are stored in variables. Only the first record is saved if multiple hits are returned.

From the example above, the following variables are declared:

nexus.RECORDCOUNT
nexus.ISVIP

Note: Oracle always returns field names in uppercase via ODBC. The variables are accordingly created with capital letters, regardless of how they are written in the query itself.

Object outputs

Output	Use when
Found	1 or more data sets returned by the query
Not found	0 data sets returned by the query.
Error	An error occurred in the query or the connection to the database.