CCS Director SQL question

[chadd39]

I have created a IVR application in which customers can call in and access their account information.

I am using the ECC ver 4.35. Right now it is working perfectly. The customer calls in, enteres their account number and zip. I store those values in a cp field, make sure they match a record line in the Oracle table and if they do, I give them their account balance, next payment due and payment due date. These SQLExecute actions are all SELECT statements.

I would like to populate another table so we have record of what the automated system told the customer and also use this table to analize who is using our automated system. I have created an Insert statement that I cannot seem to make work. I am sure it is a syntax issue.. Anyone have any suggestions? This is what I have in there now..

insert into PWD_IVR_CALLED
select * from PWD_IVR
WHERE PWD_IVR.acctno = '%acctnum%';

I am trying to say, populate table PWD_IVR_CALLED from PWD_IVR with the complete row of information for account number that is stored in the CP Filed %acctnum%

Thanks...

[chadd39]

Insert Statement

Has anyone ever used an insert statement within the ECC? Just wondering if insert even works within the CCS

[Contractor]

Inserts do work.

Do this.

1. Make a new table to insert your data into, mytable for example. Give the table columns that will match up with the call profiles you are going to insert.. i.e. ani, acctnum, zip, etc. Make new call profiles in the ECC Director for any information you want to insert

2. Make a new SQL Execute action and insert it into your script where it seems appropriate.. (I would insert it before you announce back to the customer the data they want so you capture the data on the call even if the customer hangs up after they get what they want.)

3. Make sure you have actions to populate all the call profiles you create.. ani and dnis get populated for you.

4. Put this statement (or modify it for your use) into your SQL Execute action:

insert into mytable (ani, dnis, acctnum, zip, date) values (%ani%, %dnis%, %acctnum%, %zip%, sysdate);

That should do it. Let me know if you want help.

[chadd39]

Inserts

Hello.

Your information has been helpful. I am making progress thanks to you and I am actually inserting data now!!!

My problem is that I do not want to populate table STEVE_PWD_IVR_CALLED with CP fileds. I want to populate with data from another table named STEVE_PWD_IVR. This table has all of the account data that we are giving to the customer. The way you have it formated, I would need to create a CP field for each value in STEVE_PWD_IVR. I can do this, but there are 40 fields in the table.

For testing I have done this in an SQLEXECUTE action

Insert into steve_pwd_ivr_called
(ACCTKEY, TOTALBAL,
WATERBAL, SEWERBAL, PAYDATE, PAYMENT, POSTFLAG,
BLKEY, BILLDATE, DUEDATE, PRINCIPAL, PENALTY, BLTO,
BLFROM, DLNQDATE, BLRDDATE, PERIOD, NEXTDUEDT,
INSTDUEAMT, ACCTNO, ACCTSTAT, ACCTCLASS, ACCTGRP,
CUSTDESC, ADDRKEY, STNO, STNAME, STREETADDRESS,
CITY, ZIP, DUNLEVEL, ARNGDWNAMT, ARNGDWNDUE,
NXTBLDATE, LASTVALPADT, BILLZIP, CUTDATE, THETIME,
TRACK)

values

(%balance%, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null, null, null, null,
null, null, null);

This populates acctkey in steve_pwd_ivr_called with the %balance% cp field. The value of the CP field balance is gotten by quering steve_pwd_ivr for the account number that the customer entered. I have attached a screen shot of how %balance% is created.

I was hoping to populate the whole record line in one swoop by somehow saying copy everything in record line x from STEVE_PWD_IVRto STEVE_PWD_IVR_CALLED where the account number is equal to what the customer entered. (%acctnum%)

Your method means that in order to get every value over to the STEVE_PWD_IVR_CALLED table, I need to create 40 sqlexecute statement for each field name and assign it to a cp field and then insert it that way. Your method works great if there ARE only a few values. If it has to be done that way, I guess i need to get to work, but any other suggestions would be welcomed..

Thanks

[Contractor]

I see.. yeah.. I don't know how to do it any other way..

If you figure it out, post it and share.

[chadd39]

I have started down your path. I am still having issues. First i created a script called CPDECLARATIONS. Here it is:

SELECT STEVE_PWD_IVR.ACCTKEY as acctkey,
steve_pwd_ivr.TOTALBAL as totalbal,
steve_pwd_ivr.WATERBAL as waterbal,
steve_pwd_ivr.SEWERBAL as sewerbal,
steve_pwd_ivr.PAYDATE as paydate,
steve_pwd_ivr.PAYMENT as payment,
steve_pwd_ivr.POSTFLAG as postflag,
steve_pwd_ivr.BLKEY as blkey,
steve_pwd_ivr.BILLDATE as billdate,
steve_pwd_ivr.DUEDATE as duedate,
steve_pwd_ivr.PRINCIPAL as principal,
steve_pwd_ivr.PENALTY as penalty,
steve_pwd_ivr.BLTO as blto,
steve_pwd_ivr.BLFROM as blfrom,
steve_pwd_ivr.DLNQDATE as dlnqdate,
steve_pwd_ivr.BLRDDATE as blrddate,
steve_pwd_ivr.PERIOD as period,
steve_pwd_ivr.NEXTDUEDT as nextduedt,
steve_pwd_ivr.INSTDUEAMT as instdueamt,
steve_pwd_ivr.ACCTNO as acctno,
steve_pwd_ivr.ACCTSTAT as acctstat,
steve_pwd_ivr.ACCTCLASS as acctclass,
steve_pwd_ivr.ACCTGRP as acctgrp,
steve_pwd_ivr.CUSTDESC as custdesc,
steve_pwd_ivr.ADDRKEY as addrkey,
steve_pwd_ivr.STNO as stno,
steve_pwd_ivr.STNAME as stname,
steve_pwd_ivr.STREETADDRESS as streetaddress,
steve_pwd_ivr.CITY as city,
steve_pwd_ivr.ZIP as zip,
steve_pwd_ivr.DUNLEVEL as dunlevel,
steve_pwd_ivr.ARNGDWNAMT as arngdwnamt,
steve_pwd_ivr.ARNGDWNDUE as arngdwndue,
steve_pwd_ivr.NXTBLDATE as nxtbldate,
steve_pwd_ivr.LASTVALPADT as lastvalpadt,
steve_pwd_ivr.BILLZIP as billzip,
steve_pwd_ivr.CUTDATE as cutdate,
steve_pwd_ivr.THETIME as thetime,
steve_pwd_ivr.TRACK as track
From steve_pwd_ivr
WHERE STEVE_PWD_IVR.ACCTNO = '%acctnum%';

I next have created my insert statement:

Insert into steve_pwd_ivr_called (ACCTKEY, TOTALBAL,
WATERBAL, SEWERBAL, PAYDATE, PAYMENT, POSTFLAG,
BLKEY, BILLDATE, DUEDATE, PRINCIPAL, PENALTY, BLTO,
BLFROM, DLNQDATE, BLRDDATE, PERIOD, NEXTDUEDT,
INSTDUEAMT, ACCTNO, ACCTSTAT, ACCTCLASS, ACCTGRP,
CUSTDESC, ADDRKEY, STNO, STNAME, STREETADDRESS,
CITY, ZIP, DUNLEVEL, ARNGDWNAMT, ARNGDWNDUE,
NXTBLDATE, LASTVALPADT, BILLZIP, CUTDATE, THETIME,
TRACK)
values
(%ACCTKEY%, %TOTALBAL%,
%WATERBAL%, %SEWERBAL%, null, %PAYMENT%, %POSTFLAG%,
%BLKEY%, null, null, %PRINCIPAL%, %PENALTY%, null,
null, null, null, %PERIOD%, null,
%INSTDUEAMT%, %ACCTNO%, %ACCTSTAT%, %ACCTCLASS%, %ACCTGRP%,
%CUSTDESC%, %ADDRKEY%, %STNO%, %STNAME%, %STREETADDRESS%,
%CITY%, %ZIP%, %DUNLEVEL%, %ARNGDWNAMT%, %ARNGDWNDUE%,
null, null, %BILLZIP%, null, null,
%TRACK%);

Here are the field types:

ACCTKEY NOT NULL NUMBER(9)
TOTALBAL NUMBER
WATERBAL NUMBER
SEWERBAL NUMBER
PAYDATE DATE
PAYMENT NUMBER
POSTFLAG VARCHAR2(1)
BLKEY NUMBER
BILLDATE DATE
DUEDATE DATE
PRINCIPAL NUMBER
PENALTY NUMBER
BLTO DATE
BLFROM DATE
DLNQDATE DATE
BLRDDATE DATE
PERIOD NUMBER
NEXTDUEDT DATE
INSTDUEAMT FLOAT(126)
ACCTNO VARCHAR2(24)
ACCTSTAT VARCHAR2(6)
ACCTCLASS VARCHAR2(6)
ACCTGRP VARCHAR2(10)
CUSTDESC VARCHAR2(30)
ADDRKEY NUMBER(9)
STNO VARCHAR2(6)
STNAME VARCHAR2(20)
STREETADDRESS VARCHAR2(57)
CITY VARCHAR2(20)
ZIP VARCHAR2(10)
DUNLEVEL NUMBER
ARNGDWNAMT FLOAT(126)
ARNGDWNDUE DATE
NXTBLDATE DATE
LASTVALPADT DATE
BILLZIP VARCHAR2(20)
CUTDATE DATE
THETIME VARCHAR2(8)
TRACK NUMBER

Problems are:

If a field in steve_pwd_ivr is blank, script will fail out. I remied this by making sure there is at least a 0 in a field if it is blank.

Date fields will not insert over. I have nulled them for now. I have no idea why they will not insert

Certain fields cannot use just %cpfield% they need '%cpfield%'. Even then, somtimes the value that gets inserted into steve_pwd_ivr_called is %. Attached is the data that is currently being inserted. Does anyone have any ideas? Basically, shoretel is not complying with basic SQL syntax,

[chadd39]

I have been unable to insert all records. I am now just going to insert %acctno% into a holding table when a customer calls. At the end of the day, I have our DB guy insert all of the records who have their account number poplated in the holding table. Since the table steve_pwd_ivr is a daily static cut, this is working good.

[Contractor]

I would use a trigger to fire after insert.

[chadd39]

Trigger is the path we ended up going. Works slick and is much less cumbersome than trying to do the insert through shoretel