Maximo List Archive

From: Warren, Jade GRE-MG (2015-09-17 13:01)

We have implemented the e-mail listener, and found it necessary to "undo" several of the "features" of the listener for it to meet our business cases. Out of the box, the listener:
* Does NOT process communications for work orders
* Does NOT process communications related to closed tickets
* Does NOT process communications sent from external parties that do not have a person record, even though the communications may be related to a valid ticket
Although we do not have a need to create work orders, we did need to meet the cases listed above. We chose to use a trigger to accomplish that processing.
We did also put together an automation script to process "tags" in the subject to perform additional actions on a ticket.
Both items are attached.
Jade E Warren
Senior Systems Analyst
Great River Energy
12300 Elm Creek Boulevard
Maple Grove, MN 55369
* (763) 445-5539 - Direct
* (612) 801-0191 - Mobile
* (763) 445-6339 - Fax
www.GreatRiverEnergy.com<http://www.greatriverenergy.com/>
NOTICE TO RECIPIENT: The information contained in this message from
Great River Energy and any attachments are confidential and intended
only for the named recipient(s). If you have received this message in
error, you are prohibited from copying, distributing or using the
information. Please contact the sender immediately by return email and
delete the original message.

----------
from psdi.server import MXServer;
from psdi.mbo import MboConstants;
from psdi.mbo import Mbo;
if (mbo.getString("HISTORYFLAG") == "N"):
## Do initial clean-up ##

## Process self-assignments #ME tag ##
vD = mbo.getString("DESCRIPTION");
vDu = vD.upper();
l = len(vD);
x = vDu.find("#ME");
if (x != -1):
## Clear the PERSONGROUP_GRE field
mbo.setValue("PERSONGROUP_GRE","");
## Determine whether the REPORTEDBY individual belongs to a QUEUE group
clause="persongroup in (select persongroup from persongroupteam where '"+mbo.getString("REPORTEDBY")+"' = respparty) and type='QUEUE' "
persongset=MXServer.getMXServer().getMboSet("PERSONGROUP", mbo.getUserInfo());
persongset.setWhere(clause);

if(persongset.count() > 0): ## As long as the person belongs to AT LEAST 1 QUEUE group, we can proceed with ownership assignment
mbo.setValue("OWNER", mbo.getString("REPORTEDBY"));
if(persongset.count()==1): ## If the person belongs to EXACTLY 1 QUEUE group, we can also assign their group for them
mbo.setValue("PERSONGROUP_GRE",persongset.getMbo(0).getString("PERSONGROUP"));
if(persongset.count()==0): ## If the person belongs to NO QUEUE groups (probably due to drag-and-drop), assign to '2252'
mbo.setValue("PERSONGROUP_GRE", "2252");
## Remove tag from the DESCRIPTION field
vD=vD[:x] + vD[x+3:l];
mbo.setValue("DESCRIPTION", vD);
----------
DROP TRIGGER MAXIMO.INBOUNDCOMM_GRE_T;
CREATE OR REPLACE TRIGGER MAXIMO.INBOUNDCOMM_GRE_T
BEFORE INSERT OR UPDATE ON MAXIMO.INBOUNDCOMM
FOR EACH ROW
DECLARE
rID VARCHAR2(30);
s NUMBER;
e NUMBER;
tcount NUMBER;
rcount NUMBER;
acount NUMBER;
woid NUMBER;
wocl VARCHAR2(30);
tkid NUMBER;
tknum NUMBER;
ldid NUMBER;
tkstatusid NUMBER;
worklogid NUMBER;
tkcl VARCHAR2(30);
newCOMMLOGID NUMBER;
newCOMMLOaGID NUMBER;
newCOMMLOGUID NUMBER;
error_message VARCHAR2(400);
CURSOR attach (InitID NUMBER) IS SELECT * FROM maximo.doclinks WHERE ownertable = 'INBOUNDCOMM' AND ownerid = InitID;
att_rec attach%rowtype;
BEGIN
IF (upper(:new.replyto) LIKE 'MAILER-DAEMON%') OR (upper(:new.sendfrom) LIKE 'MAILER-DAEMON%') THEN
:new.replyto := '';
ELSE
IF ((:new.status = 'INVALID') AND (:new.error NOT LIKE '%emaillstner#invsubject%')) OR
(:new.status = 'ERROR' AND :new.error LIKE 'Person does not exist%' AND :new.subject LIKE '%##%##%') OR
(:new.status = 'ERROR' AND :new.error LIKE '%BMXAA4350E%' AND :new.subject LIKE '%##%##%') THEN -- check to see if the row was not normally processed and if there is an OBJECTKEY
IF (:new.objectkey IS NULL) THEN
s := instr(:new.subject, '##', 1);
e := instr(:new.subject, '##', -1);
IF (s <> e AND s <> 0) THEN
rID := substr(:new.subject, s+2, e-s-2);
:new.objectkey := rID;
:new.subject := replace(:new.subject, '##' || rID || '##', '');
END IF;
END IF;
IF (:new.objectkey IS NOT NULL) THEN
SELECT count(*) INTO rcount FROM maximo.workorder WHERE wonum = :new.objectkey;
SELECT count(*) INTO tcount FROM maximo.ticket WHERE ticketid = :new.objectkey;
END IF;
IF ((:new.changeby IS NULL OR :new.changeby = 'MAXADMIN') AND :new.status = 'ERROR' AND :new.error LIKE 'Person does not exist%') THEN
:new.changeby := 'EXTERNAL';
:new.personid := 'EXTERNAL';
END IF;

IF (tcount = 1) AND (:new.status <> 'COMP_CL') THEN -- we are dealing with an existing ticket...
-- Get ticket identifying information to tag COMMLOG table appropriately
SELECT ticketuid INTO tkid FROM maximo.ticket WHERE ticketid = :new.objectkey;
SELECT class INTO tkcl FROM maximo.ticket WHERE ticketid = :new.objectkey;
-- Tag the row as having been processed...
:new.status := 'COMP_CL';
:new.error := 'Added to TICKET communications log.';
:new.objectname := tkcl;
:new.mailid := :new.mailid || 'x';
-- Check to avoid double-entry
IF (:new.mailid = 'x') THEN
-- Get next COMMLOGID FROM AUTOKEY table
SELECT seed + 1 INTO newCOMMLOGID FROM autokey WHERE autokeyname = 'COMMLOGID';
UPDATE autokey SET seed = seed + 1 WHERE autokeyname = 'COMMLOGID';
-- Get next COMMLOGUID FROM sequence
SELECT commlogseq.nextval INTO newCOMMLOGUID FROM dual;
-- Create the COMMLOG entry
INSERT INTO maximo.commlog (commlogid, commloguid, sendto, sendFROM, subject, createby, createdate, orgobject,
ownerid, ownertable, cc, bcc, replyto, inbound, message, keepfailed, issendfail)
VALUES (newCOMMLOGID, newCOMMLOGUID, :new.sendto, :new.sendFROM, :new.subject, :new.personid, :new.receivedate, 'TRIGGER',
tkid, tkcl, :new.cc, :new.bcc, :new.replyto, 1, :new.msgbody, 0, 0);

-- Find out of there is an attachment which was processed...
SELECT count(*) INTO acount FROM maximo.doclinks WHERE ownertable = 'INBOUNDCOMM' AND ownerid = :new.inboundcommid;
IF (acount > 0) THEN

OPEN attach(:new.inboundcommid);
LOOP
FETCH attach INTO att_rec;
EXIT WHEN attach%notfound;
INSERT INTO maximo.doclinks (document, ownertable, ownerid, doctype, getlatestversion, createby, createdate,
changeby, changedate, printthrulink, copylinktowo, doclinksid, docinfoid)
VALUES (att_rec.document, tkcl, tkid, att_rec.doctype, 1, att_rec.createby, att_rec.createdate,
att_rec.changeby, att_rec.changedate, 1, 0, doclinksseq.nextval, att_rec.docinfoid
);
END LOOP;
END IF;
END IF;
ELSIF (rcount = 1) THEN -- we are dealing with an existing workorder...
-- Get work order identifying information to tag COMMLOG table appropriately
SELECT workorderid INTO woid FROM maximo.workorder WHERE wonum = :new.objectkey;
SELECT woclass INTO wocl FROM maximo.workorder WHERE wonum = :new.objectkey;
-- Tag the row as having been processed...
:new.status := 'COMP_CL';
:new.error := 'Added to WORKORDER communications log.';
:new.objectname := wocl;
:new.mailid := :new.mailid || 'x';
-- Check to avoid double-entry
IF (:new.mailid = 'x') THEN
-- Get next COMMLOGID FROM AUTOKEY table
SELECT seed + 1 INTO newCOMMLOGID FROM autokey WHERE autokeyname = 'COMMLOGID';
UPDATE autokey SET seed = seed + 1 WHERE autokeyname = 'COMMLOGID';
-- Get next COMMLOGUID FROM sequence
SELECT commlogseq.nextval INTO newCOMMLOGUID FROM dual;
-- Create the COMMLOG entry
INSERT INTO maximo.commlog (commlogid, commloguid, sendto, sendFROM, subject, createby, createdate, orgobject,
ownerid, ownertable, cc, bcc, replyto, inbound, message, keepfailed, issendfail)
VALUES (newCOMMLOGID, newCOMMLOGUID, :new.sendto, :new.sendFROM, :new.subject, :new.personid, :new.receivedate, 'TRIGGER',
woid, wocl, :new.cc, :new.bcc, :new.replyto, 1, :new.msgbody, 0, 0);
-- Find out of there is an attachment which was processed...
SELECT count(*) INTO acount FROM maximo.doclinks WHERE ownertable = 'INBOUNDCOMM' AND ownerid = :new.inboundcommid;
IF (acount > 0) THEN

OPEN attach(:new.inboundcommid);
LOOP
FETCH attach INTO att_rec;
EXIT WHEN attach%notfound;
INSERT INTO maximo.doclinks (document, ownertable, ownerid, doctype, getlatestversion, createby, createdate,
changeby, changedate, printthrulink, copylinktowo, doclinksid, docinfoid)
VALUES (att_rec.document, 'WO' || wocl, woid, att_rec.doctype, 1, att_rec.createby, att_rec.createdate,
att_rec.changeby, att_rec.changedate, 1, 0, doclinksseq.nextval, att_rec.docinfoid
);
END LOOP;
END IF;
END IF;
ELSIF (:new.objectkey IS NOT NULL) THEN
:new.error := 'Record does not exist for object SR for inbound communication ' || :new.inboundcommid || '.';
:new.status := 'INVALID';
END IF;
ELSIF ((:new.status = 'INVALID') AND (:new.error LIKE '%emaillstner#invsubject%')) OR
(:new.status = 'ERROR' AND
((:new.error LIKE 'Person does not exist%' AND :new.subject NOT LIKE '%##%##%') OR (:new.error LIKE '%BMXAA4350E%' AND :new.subject NOT LIKE '%##%##%'))) THEN -- Attempt to catch situations where the SENDFROM is not matched to a PERSON record and a new ticket is needed
BEGIN
IF (:new.subject IS NULL or :new.subject = '') THEN
:new.subject := '<empty subject>';
END IF;

IF (:new.changeby IS NULL OR :new.changeby = 'MAXADMIN') THEN
:new.changeby := 'EXTERNAL';
:new.personid := 'EXTERNAL';
END IF;

/* Find the next available value of TICKET.TICKETID */
SELECT SEED + 1 INTO tknum FROM autokey WHERE autokeyname = 'SRID';
UPDATE autokey SET seed = seed + 1 WHERE autokeyname = 'SRID';

/* Get UIDs for all other tables */
SELECT ticketseq.nextval INTO tkid FROM dual;
SELECT tkstatusseq.nextval INTO tkstatusid FROM dual;
SELECT worklogseq.nextval INTO worklogid FROM dual;
SELECT longdescriptionseq.nextval INTO ldid FROM dual;

/* Create a new ticket (row) in MAXIMO.TICKET */
INSERT INTO MAXIMO.TICKET
(statusdate, changedate, reportdate, description, reportedby, reportedemail, affectedperson, changeby,
externalrecid, ticketuid, ticketid, class, status, source, externalsystem,
langcode, dept_gre, assetsiteid, siteid, assetorgid, orgid,
hasld, isglobal, relatedtoglobal, sitevisit, inheritstatus, isknownerror,
historyflag, template, hasactivity, actlabhrs, actlabcost,
commsend_gre, noc700mhz_gre, nonproduction_gre, selfservsolaccess,
hassolution, pluspporeq)
VALUES (:new.receivedate, :new.receivedate, :new.receivedate, :new.subject, 'EXTERNAL', :new.sendfrom, 'EXTERNAL', 'SYSTEM',
:new.inboundcommid, tkid, tknum, 'SR', 'REGISTERED', 'EMAIL', 'EMAIL',
'EN', 'IT', 'TRANS','TRANS','GRE','GRE',
1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

/* UPDATE the TKSTATUS table */
INSERT INTO MAXIMO.TKSTATUS (changeby, changedate, class, memo, orgid, siteid, status, ticketid, tkstatusid)
VALUES ('SYSTEM', :new.receivedate, 'SR', 'Created through E-mail listener',
'GRE', 'TRANS', 'REGISTERED', to_char(tkid), tkstatusid);

/* UPDATE the WORKLOG (Result) table */
INSERT INTO MAXIMO.WORKLOG (WORKLOGID, LOGTYPE, CREATEBY, CREATEDATE, CLIENTVIEWABLE, SITEID, ORGID, MODIFYBY, MODIFYDATE, RECORDKEY, CLASS, LANGCODE, HASLD, WORKDATE_GRE)
VALUES (worklogid, 'RESULT', 'SYSTEM', :new.receivedate, 1, 'TRANS', 'GRE', 'SYSTEM', :new.receivedate, to_char(tkid), 'SR', 'EN', 0, :new.receivedate);

/* UPDATE the LONGDESCRIPTION table for the Ticket */
INSERT INTO MAXIMO.LONGDESCRIPTION (LANGCODE, LDKEY, LDOWNERCOL, LDOWNERTABLE, LDTEXT, LONGDESCRIPTIONID,CONTENTUID)
VALUES ('EN', tkid, 'DESCRIPTION', 'TICKET',
:new.sendfrom || chr(10) || chr(13) || :new.msgbody, ldid, ldid);

/* UPDATE the WORKVIEW table */
INSERT INTO MAXIMO.WORKVIEW (affectedperson, app, class, description, historyflag, istask, orgid,
ownerid, recordkey, reportdate, reportedby, siteid, status, workviewid)
VALUES ('EXTERNAL', 'SR', 'SR', :new.subject, 0, 0, 'GRE',
tkid, tknum, :new.receivedate, 'EXTERNAL', 'TRANS', 'REGISTERED', workviewseq.nextval);

/* ADD a COMMLOG entry */
-- Get next COMMLOGID FROM AUTOKEY table
SELECT seed + 1 INTO newCOMMLOGID FROM autokey WHERE autokeyname = 'COMMLOGID';
UPDATE autokey SET seed = seed + 1 WHERE autokeyname = 'COMMLOGID';
-- Get next COMMLOGUID FROM sequence
SELECT commlogseq.nextval INTO newCOMMLOGUID FROM dual;
-- Create the COMMLOG entry
INSERT INTO maximo.commlog (commlogid, commloguid, sendto, sendFROM, subject, createby, createdate, orgobject,
ownerid, ownertable, cc, bcc, replyto, inbound, message, keepfailed, issendfail)
VALUES (newCOMMLOGID, newCOMMLOGUID, :new.sendto, :new.sendFROM, :new.subject, :new.personid, :new.receivedate, 'TRIGGER',
tkid, 'SR', :new.cc, :new.bcc, :new.replyto, 1, :new.msgbody, 0, 0);
-- Find out of there is an attachment which was processed...
SELECT count(*) INTO acount FROM maximo.doclinks WHERE ownertable = 'INBOUNDCOMM' AND ownerid = :new.inboundcommid;
IF (acount > 0) THEN

OPEN attach(:new.inboundcommid);
LOOP
FETCH attach INTO att_rec;
EXIT WHEN attach%notfound;
INSERT INTO maximo.doclinks (document, ownertable, ownerid, doctype, getlatestversion, createby, createdate,
changeby, changedate, printthrulink, copylinktowo, doclinksid, docinfoid)
VALUES (att_rec.document, 'SR', tkid, att_rec.doctype, 1, att_rec.createby, att_rec.createdate,
att_rec.changeby, att_rec.changedate, 1, 0, doclinksseq.nextval, att_rec.docinfoid
);
END LOOP;
END IF;

/* UPDATE status as PROCESSED */
:new.objectkey := tknum;
:new.action := 'CREATE';
:new.objectname := 'SR';
:new.status := 'COMP_NEW';
:new.error := 'New ticket created';
EXCEPTION
WHEN others THEN
BEGIN
error_message := substr(sqlerrm(sqlcode), 1, 400);
:new.error := error_message;
:new.status := 'ERROR';

/* Remove rows that were inserted because this trigger was fired */
DELETE FROM MAXIMO.TICKET WHERE TICKETUID = tkid;
DELETE FROM MAXIMO.TKSTATUS WHERE TKSTATUSID = tkstatusid;
DELETE FROM MAXIMO.LONGDESCRIPTION WHERE LDKEY = tkid AND ldownertable = 'TICKET';
DELETE FROM MAXIMO.WORKVIEW WHERE OWNERID = tkid;
END;
END;
END IF;
END IF;
EXCEPTION
WHEN others THEN
BEGIN
error_message := substr(sqlerrm(sqlcode), 1, 400);
:new.error := error_message;
END;
END;
/