cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
2070
Views
0
Helpful
6
Replies

IVR Scripting with CUE Unity Express

b.zont
Level 1
Level 1

Hi,

We need to create an IVR script for a client to retreive information from an Oracle DB to compare the information received from the caller through IVR menus with the info in the DB ( like PIN numbers etc.). I can see that by enabling the IVR options, it is possible to read info from an SQL DB. I just want to be sure if what is required is really possible and if we need to consider any issues. Below are some examples of the SQL queries we need to run from thie CUE IVR script. If someone can give some ideas and tell if this is possible or not, that would be great.

[LOGIN]

dsn=oracle_xxx

readsql=SELECT u.pin, COALESCE(c.clid, u.clid) clid, u.user_group FROM t_users u LEFT JOIN clid_implicit_user c ON c.user_pin = u.pin AND c.clid = '${SQL_ESC(${ARG1})}' WHERE u.pin = COALESCE('${SQL_ESC(${ARG2})}', c.user_pin) AND COALESCE(u.clid, '${SQL_ESC(${ARG1})}', '*') = COALESCE('${SQL_ESC(${ARG1})}', '*')

[ACCESS]

dsn=oracle_xxx

readsql=WITH login_user AS (SELECT u.pin, COALESCE(c.clid, u.clid) clid, u.user_group FROM t_users u LEFT JOIN clid_implicit_user c ON c.user_pin = u.pin AND c.clid = '${SQL_ESC(${ARG1})}' WHERE u.pin = COALESCE('${SQL_ESC(${ARG2})}', c.user_pin) AND COALESCE(u.clid, '${SQL_ESC(${ARG1})}', '*') = COALESCE('${SQL_ESC(${ARG1})}', '*') ) SELECT COALESCE(p.aircraft_id, '${SQL_ESC(${ARG3})}') aesid FROM login_user u LEFT JOIN t_fleet f ON f.pin = u.pin LEFT JOIN t_plane p ON p.fleet_id = f.fleet_id WHERE COALESCE(p.aircraft_id, '${SQL_ESC(${ARG3})}') = '${SQL_ESC(${ARG3})}'

[GES]

dsn=oracle_xxx

readsql=WITH login_user AS ( SELECT u.pin, COALESCE(c.clid, u.clid) clid, u.user_group FROM t_users u LEFT JOIN clid_implicit_user c ON c.user_pin = u.pin AND c.clid = '${SQL_ESC(${ARG1})}' WHERE u.pin = COALESCE('${SQL_ESC(${ARG2})}', c.user_pin) AND COALESCE(u.clid, '${SQL_ESC(${ARG1})}', '*') = COALESCE('${SQL_ESC(${ARG1})}', '*')), ordered_aes_login AS ( SELECT  ges,ROW_NUMBER() OVER (PARTITION BY aesid ORDER BY currdt DESC, currti DESC, refno DESC NULLS LAST) ranking FROM logon_logoff_raw WHERE calltype = '50' AND aesid = '${SQL_ESC(${ARG3})}' ), last_aes_ges_login AS ( SELECT ges FROM ordered_aes_login WHERE ranking = 1 ) SELECT l.ges, c.rds FROM login_user u INNER JOIN last_aes_ges_login l ON 1=1 INNER JOIN rds_cfg c ON c.user_group = u.user_group AND l.ges = c.ges

Thanks

Bora