IVR Scripting with CUE Unity Express

[b.zont]

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

[paolo bevilacqua]

The CUE itselff will not due any SQL quesry.

You need to write a server-side module for CUE advanced IVR.

It can then do anything you want.

[b.zont]

Hi Paolo,

When you say server side module, you are talking about a custom script written on Cisco Unified Communications Express Editor, an .aef script, right ?

So call comes in, hits the dial-peer, send to CUE via SIP and hit the ccn trigger which runs this aef script on CUE.

Inside this script I will have the DB GET, DB READ, etc. steps. And as long as standard SQL queries anything can be done, right ?

Thanks.

[paolo bevilacqua]

No I did not meant that.

Please read

http://www.cisco.com/en/US/docs/voice_ip_comm/unity_exp/rel7_0/administrator/script/scripteditor.html

[b.zont]

Hi Paolo,

I am not a programmer even not very good with scripts. So Is it possible for you to make it a bit more clear for me. I read the document in the link already and also the

http://www.cisco.com/en/US/docs/voice_ip_comm/unity_exp/rel7_0/administrator/ivr/dbprofle.html

And what I understood is what I described above : Call comes in --> SIP Dial-per to CUE ---> ccn trigger on cue to trigger aef script.---> in the script use DB steps and describe the DB on CUE CLI also.

If this is not what you meant and/or this is not correct and/or possible, then what does "server side module for CUE advanced IVR"means ?

Thanks

Bora

[paolo bevilacqua]

It means a web server that communicates with CUE via HTTP/vxml, and does anything that is needed to be done.

And yes you need to contact a good programmer.

[b.zont]

Hi,

I am a bit confused then.

Can you please tell me what it is trying to tell in the below link as it seems to me the opposite of what you are saying :

http://www.cisco.com/en/US/docs/voice_ip_comm/unity_exp/rel7_0/administrator/script/stpref.html#wp1055695

Which starts with : "

Database Steps (IVR Only)

The steps in the Database palette  provide designers with the steps to read and write data from database  tables and views. For more information about setting up the database,  see the Cisco Unity Express 7.0 IVR CLI Administrator Guide.

"

And also it says MS SQL, Oracle, etc are supported.

Doesn't this mean that from the CUE scripts , we can do SQL queries directly ?

Cheers