ERMS CRM - Closing a case via a email

Here is a great sample of using ERMS within CRM to close a case via a email.

The first thing you will need to do is have your exchange admin setup a email account to send to. (CRMClose is an example alias)

One you have that setup, you will need to setup this account within ERMS.


Here is a query to view all email transaction coming in:

SELECT M.MCF_EMAIL_ID
, M.MCF_EMAIL_FROM
, M.MCF_EMAIL_SENDER
, M.MCF_UID
, M.MCF_WL_SUBJECT
, M.MCF_DTTM_SENT
, M.MCF_ATTACH_LIST
, M.MCF_ATTACH_SIZES
, M.MCF_IS_ATT_URL
, M.MCF_ATT_URL
, COALESCE(P.MCF_EMAIL_TEXT
, M.MCF_EMAIL_TEXT)
, M.MCF_CONTENT_TYPE
, IE.MAILBOX_ID
, IE.BUSINESS_UNIT
FROM PS_MCFEM_MAIL_MAIN M
, PS_MCFEM_MAIL_PART P
, PS_RB_IN_EMAIL IE
WHERE M.MCF_EMAIL_ID = P.MCF_EMAIL_ID (+)
AND M.MCF_EMAIL_ID = IE.MCF_EMAIL_ID (+)
AND M.MCF_EMAIL_STATUS = 0
AND P.MCF_EMAIL_PARTNO (+) = 1
AND IE.STRUCTURED_SW = 'N'
AND (m.MCF_EMAIL_FROM <> 'support@asu.edu'
AND m.MCF_EMAIL_FROM <> 'postmaster@exchange.asu.edu')


Send an email to your newly created account and verify that your subject and email text is within the records after the ERMS process is ran.

Using this format for this example:

Sent to: CRMClose
Subject: 343514#
Text: Please close case. Resolved JM


Here is the peoplecode to make it all happen within a App Engine process. Make sure to setup a state record to store the values from the query above.


/*** ASU Custom Mod - 12/2008 - J.McMahon ***/
/*** Create new logic to handle close case and update case note ***/
Declare Function GetFilePathServer PeopleCode ASU_FILEPATH_WK.ECFILELISTPATH FieldFormula;

Local ApiObject &Session;
Local ApiObject &asuCI;
Local boolean &genUse;

Local File &fileLog;
Local ApiObject &oSession, &oRcCaseCi, &oRcSolutionCI;
Local ApiObject &oRbqAdptrTmpv2Collection, &oRbqAdptrTmpv2;
Local ApiObject &oRbEmailIbVwCollection, &oRbEmailIbVw;
Local ApiObject &oRcInterestPrtCollection, &oRcInterestPrt;
Local ApiObject &oRfEntlDispCollection, &oRfEntlDisp;
Local ApiObject &oRfShowEntlCollection, &oRfShowEntl;
Local ApiObject &oRfShowPriceCollection, &oRfShowPrice;
Local ApiObject &oRcLinkCatVwCollection, &oRcLinkCatVw;
Local ApiObject &oRcCaseDisputeCollection, &oRcCaseDispute;
Local ApiObject &oRcCaseComplntCollection, &oRcCaseComplnt;
Local ApiObject &oRcResolutionCollection, &oRcResolution;
Local ApiObject &oDerivedUrIdxCollection, &oDerivedUrIdx;
Local ApiObject &oRbRidxWrk1Collection, &oRbRidxWrk1;
Local ApiObject &oRbRidxWrk2Collection, &oRbRidxWrk2;
Local ApiObject &oRbRidxWrk3Collection, &oRbRidxWrk3;
Local ApiObject &oRbRiObjVwCollection, &oRbRiObjVw;
Local ApiObject &oRcCaseNoteCollection, &oRcCaseNote;
Local ApiObject &oRcCaseAttachCollection, &oRcCaseAttach;
Local ApiObject &oRcAssocTableCollection, &oRcAssocTable;
Local ApiObject &oRcCaseSrchVwCollection, &oRcCaseSrchVw;
Local ApiObject &oEoecDsRuleVwCollection, &oEoecDsRuleVw;
Local ApiObject &oRcRelationVwCollection, &oRcRelationVw;
Local ApiObject &oRcActionHistCollection, &oRcActionHist;
Local ApiObject &oRcCaseBiCollection, &oRcCaseBi;
Local ApiObject &oRcCaseBiDetCollection, &oRcCaseBiDet;
Local ApiObject &oRcAuditVwCollection, &oRcAuditVw;
Local ApiObject &oRcTimeBiVw2Collection, &oRcTimeBiVw2;
Local ApiObject &oRbScrollL1n12Collection, &oRbScrollL1n12;
Local ApiObject &oRbScrollLvl2Collection, &oRbScrollLvl2;
Local ApiObject &oRbEmTransVwCollection, &oRbEmTransVw;
Local ApiObject &oRbWfPersidVwCollection, &oRbWfPersidVw;
Local ApiObject &oRbWfDeloptW1Collection, &oRbWfDeloptW1;
Local ApiObject &oRbWfDeloptW2Collection, &oRbWfDeloptW2;
Local ApiObject &oRfSoProdDispCollection, &oRfSoProdDisp;
Local ApiObject &oRfShowPriVwCollection, &oRfShowPriVw;
Local ApiObject &oRfAsgnPgdOutCollection, &oRfAsgnPgdOut;
Local ApiObject &oRfAsgnWkOutCollection, &oRfAsgnWkOut;
Local ApiObject &oRfAsgnWkdOutCollection, &oRfAsgnWkdOut;
Local ApiObject &oRcCaseWo2Collection, &oRcCaseWo2;
Local ApiObject &oRcResolutVwCollection, &oRcResolutVw;


Function errorHandler()
Local ApiObject &oPSMessageCollection, &oPSMessage;
Local number &i;
Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;

&oPSMessageCollection = &oSession.PSMessages;
For &i = 1 To &oPSMessageCollection.Count
&oPSMessage = &oPSMessageCollection.Item(&i);
&sErrMsgSetNum = &oPSMessage.MessageSetNumber;
&sErrMsgNum = &oPSMessage.MessageNumber;
&sErrMsgText = &oPSMessage.Text;
&fileLog.WriteLine(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText);
End-For;
rem ***** Delete the Messages from the collection *****;
&oPSMessageCollection.DeleteAll();
End-Function;


/*
Open Case Component Interface
*/
&Session = %Session;
&asuCI = &Session.GetCompIntfc(CompIntfc.RC_CASE_CI);

If None(&asuCI) Then
Error MsgGet(17831, 9114, "Message not found: Error calling GetCompIntfc");
End-If;

&genUse = False;


&subjLen = Find("#", ASU_MCFEM_AET.MCF_WL_SUBJECT);
&parsedCaseId = Substring(ASU_MCFEM_AET.MCF_WL_SUBJECT, 1, (&subjLen - 1));

If IsDigits(&parsedCaseId) Then


/*** ASU_CMCC0061 - Start Mod - J.McMahon ***/
Evaluate ASU_MCFEM_AET.MAILBOX_ID
When = "CRMClose"
/*** Close Case logic here ***/
If None(&parsedCaseId) Then
&text = ASU_MCFEM_AET.MCF_EMAIL_FROM | "," | ASU_MCFEM_AET.MCF_WL_SUBJECT | "," | ASU_MCFEM_AET.MCF_EMAIL_TEXT;
&MAIL_FLAGS = 0;
&MAIL_TO = ASU_MCFEM_AET.MCF_EMAIL_FROM;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Email case id lookup error " | "Parsed Case Id = " | &parsedCaseId;
&MAIL_TEXT = &text;
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_SENDER = "support@asu.edu";
&delim = ";";
&Content_Type = "Content-type: text/html; charset=utf8";

&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER, &delim, &Content_Type);

If Not (&RET = 0) Then
MessageBox(0, "", 0, 0, "Return status from mail = " | &RET);
End-If;

Else

try
rem ***** Set the Log File *****;

GetFilePathServer(&outFilePath, &inFilePath, %DbName);

&Process_Instance_val = ASU_MCFEM_AET.PROCESS_INSTANCE;

&fileLog = GetFile(&outFilePath | "ASU_CASE_GEN" | "_" | &Process_Instance_val | "_" | ".log", "W", %FilePath_Absolute);
&fileLog.WriteLine("Begin");
&fileLog.WriteLine("Process_Instance = : " | &Process_Instance_val);

rem ***** Get current PeopleSoft Session *****;
&oSession = %Session;

rem ***** Set the PeopleSoft Session Error Message Mode *****;
rem ***** 0 - None *****;
rem ***** 1 - PSMessage Collection only (default) *****;
rem ***** 2 - Message Box only *****;
rem ***** 3 - Both collection and message box *****;
&oSession.PSMessagesMode = 3;

rem ***** Get the Component Interface *****;
&oRcCaseCi = &oSession.GetCompIntfc(CompIntfc.RC_CASE_CI);
If &oRcCaseCi = Null Then
errorHandler();
throw CreateException(0, 0, "GetCompIntfc failed");
End-If;

&oRcSolutionCI = &oSession.GetCompIntfc(CompIntfc.RC_SOLUTION_CI);
If &oRcSolutionCI = Null Then
errorHandler();
throw CreateException(0, 0, "GetCompIntfc failed");
End-If;

rem ***** Set the Component Interface Mode *****;
&oRcCaseCi.InteractiveMode = True;
&oRcCaseCi.GetHistoryItems = True;
&oRcCaseCi.EditHistoryItems = True;


rem ***** Set the Component Interface Mode *****;
&oRcSolutionCI.InteractiveMode = True;
&oRcSolutionCI.GetHistoryItems = True;
&oRcSolutionCI.EditHistoryItems = True;



rem ***** Set Component Interface Get/Create Keys *****;
&oRcCaseCi.CASE_ID = &parsedCaseId;
&oRcCaseCi.DISP_TMPL_ID = "RC_SUPPORT";

&fileLog.WriteLine("Get and Create Keys " | "Case ID: " | &oRcCaseCi.CASE_ID);
&fileLog.WriteLine("Display Template " | &oRcCaseCi.DISP_TMPL_ID);

&oRcCaseCi.Get();

If &oRcCaseCi.RC_STATUS <> "RESOL" Then

&caseType = "CLOSED";

Local boolean &bRtn = &oRcCaseCi.SetCaseStatusToResolved();

&fileLog.WriteLine("SetCaseStatusToResolved Method Display " | &bRtn);


rem ***** Set Component Interface Get/Create Keys *****;
&oRcSolutionCI.SETID = "ASU00";
&oRcSolutionCI.SOLUTION_ID = 0;

&oRcSolutionCI.Create();

&oRcSolutionCI.RC_SOLUTION_TYPE = "ADHC";
&oRcSolutionCI.SOLN_STATUS = "ACTV";
&oRcSolutionCI.RC_SOLN_VISIBILITY = "A";
&oRcSolutionCI.RC_SUMMARY = Substring(ASU_MCFEM_AET.MCF_EMAIL_TEXT, 1, 50);
&oRcSolutionCI.SOLUTION_DESCR = ASU_MCFEM_AET.MCF_EMAIL_TEXT;
&oRcSolutionCI.EXPIRY_IND = "N";
&oRcSolutionCI.USAGE_COUNT = 0;
&oRcSolutionCI.LAST_USAGE_DTTM = %Datetime;
&oRcSolutionCI.USER_UPDATE_DTTM = %Datetime;
rem &oRcSolutionCI.UPDATED_BY_USER.Value = %OperatorId;
&oRcSolutionCI.ROW_ADDED_DTTM = %Datetime;
rem &oRcSolutionCI.ROW_ADDED_OPRID.Value = %OperatorId;
&oRcSolutionCI.ROW_LASTMANT_DTTM = %Datetime;
&oRcSolutionCI.ROW_LASTMANT_OPRID = %OperatorId;


rem ***** Execute Save *****;
If Not &oRcSolutionCI.Save() Then;
errorHandler();
throw CreateException(0, 0, "Save failed");
End-If;


rem ***** Set/Get RC_RESOLUTION Collection Field Properties -- Parent: PS_ROOT Collection *****;
&oRcResolutionCollection = &oRcCaseCi.RC_RESOLUTION;


Local integer &i1468;
For &i1468 = 1 To &oRcResolutionCollection.Count;
&oRcResolution = &oRcResolutionCollection.Item(&i1468);
&fileLog.WriteLine("&oRcResolution.RSLN_SUMMARY = " | &oRcResolution.RSLN_SUMMARY);
rem &oRcResolution.RSLN_SUMMARY = [*];
&oRcResolution.RSLN_NOTES = ASU_MCFEM_AET.MCF_EMAIL_TEXT;
&fileLog.WriteLine("&oRcResolution.RSLN_NOTES = " | &oRcResolution.RSLN_NOTES);
&fileLog.WriteLine("&oRcResolution.SELECT_FLAG = " | &oRcResolution.SELECT_FLAG);
rem &oRcResolution.SELECT_FLAG = [*];
&fileLog.WriteLine("&oRcResolution.LONG_URL = " | &oRcResolution.LONG_URL);
rem &oRcResolution.LONG_URL = [*];
&oRcResolution.SOLUTION_ID = &oRcSolutionCI.SOLUTION_ID;
&fileLog.WriteLine("&oRcResolution.SOLUTION_ID = " | &oRcResolution.SOLUTION_ID);
&oRcResolution.RSLN_STATE = "1";
&fileLog.WriteLine("&oRcResolution.RSLN_STATE = " | &oRcResolution.RSLN_STATE);

End-For;




/*&strAdHocSolution = &ciCase.GetAdHocSolutionID();
&fileLog.WriteLine("AdHocSolutionID " | &strAdHocSolution);
Local boolean &bSolRtn = &oRcCaseCi.AttemptSolution(&strAdHocSolution, "Y", ASU_MCFEM_AET.MCF_EMAIL_TEXT, "1", "3");

&fileLog.WriteLine("AttemptSOlution Method Return " | &bSolRtn);

If &bSolRtn = True Then
&oRcCaseCi.Save();
End-If; */
rem ***** Execute Save *****;

If Not &oRcCaseCi.Save() Then;
errorHandler();
throw CreateException(0, 0, "Save failed");
End-If;
¬eSubj = "Case Closed by email from ";

If &bRtn = True Then
&oRcCaseCi.Save();
End-If;

rem ***** Execute CloseCase *****;
&Rtn_val = &oRcCaseCi.CloseCase();
If &Rtn_val = True Then

&text = "A case has been closed for you via CRMClose email account. If you have any further questions or concerns, please don't hesitate to contact us." | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "Please use this link to check the status of your case:" | "
";
&text = &text | "http://www.asu.edu/go/support/managecase/" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "ASU Help Desk" | "
";
&text = &text | "Arizona State University" | "
";
&text = &text | "helpdesk@asu.edu" | "
";
&text = &text | "(480) 965-6500" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "**This is an auto-generated message****Please do not reply to this email**" | "
";

&MAIL_FLAGS = 0;
&MAIL_TO = ASU_MCFEM_AET.MCF_EMAIL_FROM;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Support case#" | &oRcCaseCi.CASE_ID | " has been closed";
&MAIL_TEXT = &text;
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_SENDER = "support@asu.edu";
&delim = ";";
&Content_Type = "Content-type: text/html; charset=utf8";



&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER, &delim, &Content_Type);

If Not (&RET = 0) Then
MessageBox(0, "", 0, 0, "Return status from mail = " | &RET);
End-If;

MessageBox(0, "", 0, 0, " case id from save = " | &oRcCaseCi.CASE_ID);

&RET_Close_Note = &oRcCaseCi.Addnote(&oRcCaseCi.CASE_ID, ¬eSubj, ASU_MCFEM_AET.MCF_EMAIL_FROM | ": " | ASU_MCFEM_AET.MCF_EMAIL_TEXT, "I", "COMNT", "E");
MessageBox(0, "", 0, 0, " adding a note = " | &RET_Close_Note);
If &RET_Close_Note Then
&oRcCaseCi.Save();
&fileLog.WriteLine("Saved Closed Case: " | &RET_Close_Note | &Rtn_val);
End-If;

End-If;
Else /* Case Status is already Resolved */
&text = "This case already has a Resolution and a RESOL status associated with. If you have any further questions or concerns, please don't hesitate to contact us." | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "Please use this link to check the status of your case:" | "
";
&text = &text | "http://www.asu.edu/go/support/managecase/" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "ASU Help Desk" | "
";
&text = &text | "Arizona State University" | "
";
&text = &text | "helpdesk@asu.edu" | "
";
&text = &text | "(480) 965-6500" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "**This is an auto-generated message****Please do not reply to this email**" | "
";

&MAIL_FLAGS = 0;
&MAIL_TO = ASU_MCFEM_AET.MCF_EMAIL_FROM;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Support case#" | &oRcCaseCi.CASE_ID | " has not been updated, because the status is already Resolved";
&MAIL_TEXT = &text;
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_SENDER = "support@asu.edu";
&delim = ";";
&Content_Type = "Content-type: text/html; charset=utf8";


&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER, &delim, &Content_Type);

If Not (&RET = 0) Then
MessageBox(0, "", 0, 0, "Return status from mail = " | &RET);
End-If;

MessageBox(0, "", 0, 0, " case id from save = " | &oRcCaseCi.CASE_ID);
End-If;
catch Exception &ex
rem Handle the exception;
&fileLog.WriteLine(&ex.ToString());
end-try;
&fileLog.WriteLine("End");
&fileLog.Close();
SQLExec(SQL.ASU_UPD_INB_EML_STATUS, ASU_MCFEM_AET.MCF_EMAIL_ID);

End-If;
/*
after createing the base ase then update the inbound erms system table so we do not process all the emails again
*/
Break;
When = "CRMAddNote"
/*** Update case note logic here ***/
If None(&parsedCaseId) Then
&text = ASU_MCFEM_AET.MCF_EMAIL_FROM | "," | ASU_MCFEM_AET.MCF_WL_SUBJECT | "," | ASU_MCFEM_AET.MCF_EMAIL_TEXT;
&MAIL_FLAGS = 0;
&MAIL_TO = ASU_MCFEM_AET.MCF_EMAIL_FROM;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Email case id lookup error " | "Parsed Case Id = " | &parsedCaseId;
&MAIL_TEXT = &text;
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_SENDER = "support@asu.edu";
&delim = ";";
&Content_Type = "Content-type: text/html; charset=utf8";

&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER, &delim, &Content_Type);

If Not (&RET = 0) Then
MessageBox(0, "", 0, 0, "Return status from mail = " | &RET);
End-If;

Else

rem ***** Set Component Interface Get/Create Keys *****;
&asuCI.CASE_ID = &parsedCaseId;
&asuCI.DISP_TMPL_ID = "RC_SUPPORT";

&asuCI.Get();

&caseType = "NOTE";
¬eSubj = "Note added by email from ";

&RET_Note = &asuCI.Addnote(&asuCI.CASE_ID, ¬eSubj, ASU_MCFEM_AET.MCF_EMAIL_FROM | ": " | ASU_MCFEM_AET.MCF_EMAIL_TEXT, "I", "COMNT", "E");
MessageBox(0, "", 0, 0, " adding a note = " | &RET);
If &RET_Note Then
&asuCI.Save();
End-If;


If ASU_MCFEM_AET.MCF_ATTACH_LIST <> " " Then

Evaluate %DbName
When "ASUCMDEV"
&attachPath = "Insert your path";
Break;
When "ASUCMTST"
&attachPath = "Insert your path";
Break;
When "ASUCMPRD"
&attachPath = "Insert your path";
Break;
When-Other
&attachPath = "Insert your path";
Break;
End-Evaluate;

&strAttachGetSql = CreateSQL("select mcf_filename,MCF_ATT_URL from PS_MCFEM_MAIL_PART where mcf_email_id = :1 AND MCF_EMAIL_PARTNO > 2", ASU_MCFEM_AET.MCF_EMAIL_ID);

SQLExec("select count(*) from PS_MCFEM_MAIL_PART where mcf_email_id = :1 AND MCF_EMAIL_PARTNO > 2", ASU_MCFEM_AET.MCF_EMAIL_ID, &ncount);
MessageBox(0, "", 0, 0, " count of attachments = " | &ncount);

&recCaseAttach = CreateRecord(Record.RC_CASE_ATTACH);
&k = 1;
While &strAttachGetSql.Fetch(&AttachName, &attachURL)
&strattachPath = "";
&len = Find("?", &attachURL);
&urlAttach = Substring(&attachURL, 1, (&len - 1));

&attachment_name = &AttachName;
&attachment_name = Left(&attachment_name, 64);
&attachment_name = Substitute(&attachment_name, " ", "_");
&attachment_name = Substitute(&attachment_name, ";", "_");
&attachment_name = Substitute(&attachment_name, "+", "_");
&attachment_name = Substitute(&attachment_name, "%", "_");
&attachment_name = Substitute(&attachment_name, "&", "_");
&attachment_name = Substitute(&attachment_name, "'", "_");
&attachment_name = Substitute(&attachment_name, "!", "_");
&attachment_name = Substitute(&attachment_name, "@", "_");
&attachment_name = Substitute(&attachment_name, "#", "_");
&attachment_name = Substitute(&attachment_name, "$", "_");

&attachment_sysname = &asuCI.CASE_ID | &attachment_name;
&strattachPath = &attachPath | &urlAttach;

MessageBox(0, "", 0, 0, " attach path = " | &strattachPath);

MessageBox(0, "", 0, 0, " file name = " | &attachment_name | " sys file name = " | &attachment_sysname);

&RETCODE = PutAttachment(URL.RC_ATTACHMENTS, &attachment_sysname, &strattachPath);
MessageBox(0, "", 0, 0, " return code = " | &RETCODE);
If &RETCODE = %Attachment_Success Then
MessageBox(0, "", 0, 0, " entered create record");
&recCaseAttach = CreateRecord(Record.RC_CASE_ATTACH);
&recCaseAttach.Setdefault();

&recCaseAttach.CASE_ID.value = &asuCI.CASE_ID;
&recCaseAttach.BUSINESS_UNIT.value = ASU_MCFEM_AET.BUSINESS_UNIT;
&recCaseAttach.NOTE_SEQ_NBR.value = 1;
&recCaseAttach.ATTACH_SEQ_NBR.value = &k;
&recCaseAttach.ATTACHUSERFILE.value = &attachment_name;
&recCaseAttach.ATTACHSYSFILENAME.value = &attachment_sysname;
&recCaseAttach.RC_VISIBILITY.value = "I";
&recCaseAttach.DESCRIPTION.value = "From email";

MessageBox(0, "", 0, 0, " info for record before save is case Id " | &asuCI.CASE_ID | " business unit " | ASU_MCFEM_AET.BUSINESS_UNIT | " note seq " | &recCaseAttach.NOTE_SEQ_NBR.value | " attach seq number " | &recCaseAttach.ATTACH_SEQ_NBR.value);

&recCaseAttach.Insert();
CommitWork();

&k = &k + 1;
End-If;
End-While;
End-If;



SQLExec(SQL.ASU_UPD_INB_EML_STATUS, ASU_MCFEM_AET.MCF_EMAIL_ID);

End-If;
Break;
When-Other /*** other than email addresses evaluated ***/

&caseType = "NEW";

/*
Try to match email address to current consumer, contact in CRM
*/
SQLExec(SQL.ASU_GET_BOID_EML, ASU_MCFEM_AET.MCF_EMAIL_FROM, &n_bo_id);
/* now lets validate and fetch the mail box id and business unit just in case there was a failure in the select
SQL */

SQLExec("select mailbox_id,business_unit from PS_RB_IN_EMAIL where mcf_email_id = :1", ASU_MCFEM_AET.MCF_EMAIL_ID, ASU_MCFEM_AET.MAILBOX_ID, ASU_MCFEM_AET.BUSINESS_UNIT);

/*
Validate business unit from email box. if no valid business unit default one

*/
If None(ASU_MCFEM_AET.BUSINESS_UNIT) Then
ASU_MCFEM_AET.BUSINESS_UNIT = "UTO00";
End-If;
/*
Grab default worklist and detemine default provider group from worklist
*/
If All(ASU_MCFEM_AET.MAILBOX_ID) Then
&sqlStrPG = "select PROVIDER_GRP_ID from ps_RF_PROVIDER_GRP where rb_wf_grp_name = (select DEFAULT_WORKLIST from ps_RB_MAILBOX_DEFN where mailbox_id = :1)";
SQLExec(SQL.ASU_GET_PG_EML, ASU_MCFEM_AET.MAILBOX_ID, &strDflPrvGrpId);
Else
Evaluate ASU_MCFEM_AET.BUSINESS_UNIT
When "UTO00"
&strDflPrvGrpId = "UTOHD";
Break;
When "ASU00"
When "ENG00"
When "HRA00"
&strDflPrvGrpId = "HRESC";
Break;
When "UTO02"
&strDflPrvGrpId = "UTOHD";
Break;
When "WPC00"
&strDflPrvGrpId = "WPCTAC";
Break;
End-Evaluate;

End-If;

If None(&strDflPrvGrpId) Then
&strDflPrvGrpId = "UTOHD";
MessageBox(0, "", 0, 0, " using the default provider group ");
End-If;

If &strDflPrvGrpId = "UTO-ATS" Then
&strStatus = "OPNEM";
Else
&strStatus = "OPEN";
End-If;

MessageBox(0, "", 0, 0, " bo id = " | &n_bo_id | " email addr = " | ASU_MCFEM_AET.MCF_EMAIL_FROM);
/*
if we did not retreive a consumer or contact bo id then we get the default user
*/
Evaluate &n_bo_id
When 0
&sqlGetAnon = "Select BO_ID from ps_bo_name where first_name like 'General' and last_name like 'Customer' and rownum = 1";
SQLExec(SQL.ASU_GET_GEN_BOID, &n_bo_id);
If None(&n_bo_id) Then
&n_bo_id = 0;
&genUse = False;
Else
&genUse = True;
End-If;
End-Evaluate;
/*
finally ready to create the case if we have a contact, consumer bo id
*/

If None(&n_bo_id) Then
<* do nothing *>
MessageBox(0, "", 0, 0, " No customer/consumer account could be found for email address = " | ASU_MCFEM_AET.MCF_EMAIL_FROM);
Else
try
&asuCI.InteractiveMode = True;
&asuCI.CASE_ID = 999999999999999;
&asuCI.DISP_TMPL_ID = "RC_SUPPORT";
&asuCI.Create();

&asuCI.BUSINESS_UNIT = ASU_MCFEM_AET.BUSINESS_UNIT;
&asuCI.RC_VERTICAL = "SW";
&asuCI.MARKET = "GBL";
&asuCI.BO_ID_CUST = &n_bo_id;
&asuCI.ROLE_TYPE_ID_CUST = 9;
&asuCI.RC_STATUS = &strStatus;
&asuCI.RC_SOURCE = "EMAIL";
&asuCI.PROVIDER_GRP_ID = &strDflPrvGrpId;


&asuCI.RC_SUMMARY = ASU_MCFEM_AET.MCF_WL_SUBJECT;
&asuCI.RC_DESCRLONG = ASU_MCFEM_AET.MCF_EMAIL_FROM | ": " | ASU_MCFEM_AET.MCF_EMAIL_TEXT;

If Not (&asuCI.Save()) Then
MessageBox(0, "", 0, 0, " error saving CI = " | &asuCI);
&asuCI.Close();
Else
/*
after createing the base ase then update the inbound erms system table so we do not process all the emails again
*/
SQLExec(SQL.ASU_UPD_INB_EML_STATUS, ASU_MCFEM_AET.MCF_EMAIL_ID);

If &genUse = True And
&asuCI.RC_STATUS = "OPEN" Then
&text = "A case has been opened for you at the Arizona State University Help Desk and a technician will be in contact with you. If you have any further questions or concerns, please don't hesitate to contact us." | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "Please use this link to check the status of your case:" | "
";
&text = &text | "http://www.asu.edu/go/support/managecase/" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "ASU Help Desk" | "
";
&text = &text | "Arizona State University" | "
";
&text = &text | "helpdesk@asu.edu" | "
";
&text = &text | "(480) 965-6500" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "**This is an auto-generated message****Please do not reply to this email**" | "
";

&MAIL_FLAGS = 0;
&MAIL_TO = ASU_MCFEM_AET.MCF_EMAIL_FROM;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Support case#" | &asuCI.CASE_ID | " has been created";
&MAIL_TEXT = &text;
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_SENDER = "support@asu.edu";
&delim = ";";
&Content_Type = "Content-type: text/html; charset=utf8";



&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER, &delim, &Content_Type);

If Not (&RET = 0) Then
MessageBox(0, "", 0, 0, "Return status from mail = " | &RET);
End-If;
End-If;


MessageBox(0, "", 0, 0, " case id from save = " | &asuCI.CASE_ID);
/*
time to add a note on the case, this will be the email body default preceeded by the from email address for reference
*/


&RET_New = &asuCI.Addnote(&asuCI.CASE_ID, ASU_MCFEM_AET.MCF_WL_SUBJECT, ASU_MCFEM_AET.MCF_EMAIL_FROM | ": " | ASU_MCFEM_AET.MCF_EMAIL_TEXT, "I", "COMNT", "E");
MessageBox(0, "", 0, 0, " adding a note = " | &RET);
If &RET_New Then
&asuCI.Save();
End-If;
/*
for the time being we are looking at attachments so we create attachment lists here. we may comment this out depending
on requirements
*/
If ASU_MCFEM_AET.MCF_ATTACH_LIST <> " " Then

Evaluate %DbName
When "ASUCMDEV"
&attachPath = "Insert your path";
Break;
When "ASUCMTST"
&attachPath = "Insert your path";
Break;
When "ASUCMPRD"
&attachPath = "Insert your path";
Break;
When-Other
&attachPath = "Insert your path";
Break;
End-Evaluate;

&strAttachGetSql = CreateSQL("select mcf_filename,MCF_ATT_URL from PS_MCFEM_MAIL_PART where mcf_email_id = :1 AND MCF_EMAIL_PARTNO > 2", ASU_MCFEM_AET.MCF_EMAIL_ID);

SQLExec("select count(*) from PS_MCFEM_MAIL_PART where mcf_email_id = :1 AND MCF_EMAIL_PARTNO > 2", ASU_MCFEM_AET.MCF_EMAIL_ID, &ncount);
MessageBox(0, "", 0, 0, " count of attachments = " | &ncount);

&recCaseAttach = CreateRecord(Record.RC_CASE_ATTACH);
&k = 1;
While &strAttachGetSql.Fetch(&AttachName, &attachURL)
&strattachPath = "";
&len = Find("?", &attachURL);
&urlAttach = Substring(&attachURL, 1, (&len - 1));

&attachment_name = &AttachName;
&attachment_name = Left(&attachment_name, 64);
&attachment_name = Substitute(&attachment_name, " ", "_");
&attachment_name = Substitute(&attachment_name, ";", "_");
&attachment_name = Substitute(&attachment_name, "+", "_");
&attachment_name = Substitute(&attachment_name, "%", "_");
&attachment_name = Substitute(&attachment_name, "&", "_");
&attachment_name = Substitute(&attachment_name, "'", "_");
&attachment_name = Substitute(&attachment_name, "!", "_");
&attachment_name = Substitute(&attachment_name, "@", "_");
&attachment_name = Substitute(&attachment_name, "#", "_");
&attachment_name = Substitute(&attachment_name, "$", "_");

&attachment_sysname = &asuCI.CASE_ID | &attachment_name;
&strattachPath = &attachPath | &urlAttach;

MessageBox(0, "", 0, 0, " attach path = " | &strattachPath);

MessageBox(0, "", 0, 0, " file name = " | &attachment_name | " sys file name = " | &attachment_sysname);

&RETCODE = PutAttachment(URL.RC_ATTACHMENTS, &attachment_sysname, &strattachPath);
MessageBox(0, "", 0, 0, " return code = " | &RETCODE);
If &RETCODE = %Attachment_Success Then
MessageBox(0, "", 0, 0, " entered create record");
&recCaseAttach = CreateRecord(Record.RC_CASE_ATTACH);
&recCaseAttach.Setdefault();

&recCaseAttach.CASE_ID.value = &asuCI.CASE_ID;
&recCaseAttach.BUSINESS_UNIT.value = ASU_MCFEM_AET.BUSINESS_UNIT;
&recCaseAttach.NOTE_SEQ_NBR.value = 1;
&recCaseAttach.ATTACH_SEQ_NBR.value = &k;
&recCaseAttach.ATTACHUSERFILE.value = &attachment_name;
&recCaseAttach.ATTACHSYSFILENAME.value = &attachment_sysname;
&recCaseAttach.RC_VISIBILITY.value = "I";
&recCaseAttach.DESCRIPTION.value = "From email";

MessageBox(0, "", 0, 0, " info for record before save is case Id " | &asuCI.CASE_ID | " business unit " | ASU_MCFEM_AET.BUSINESS_UNIT | " note seq " | &recCaseAttach.NOTE_SEQ_NBR.value | " attach seq number " | &recCaseAttach.ATTACH_SEQ_NBR.value);

&recCaseAttach.Insert();
CommitWork();

&k = &k + 1;
End-If;
End-While;
End-If;
End-If;

catch Exception &asuCIException;


end-try;
End-If;

MessageBox(0, "", 0, 0, " mail id = " | ASU_MCFEM_AET.MCF_EMAIL_ID | " and subject line = " | ASU_MCFEM_AET.MCF_WL_SUBJECT | " email from = " | ASU_MCFEM_AET.MCF_EMAIL_FROM);

End-Evaluate;

Else

&text = "Please make sure you have you case id correct. If you have any further questions or concerns, please don't hesitate to contact us." | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "Please use this link to check the status of your case:" | "
";
&text = &text | "http://www.asu.edu/go/support/managecase/" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "ASU Help Desk" | "
";
&text = &text | "Arizona State University" | "
";
&text = &text | "helpdesk@asu.edu" | "
";
&text = &text | "(480) 965-6500" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "**This is an auto-generated message****Please do not reply to this email**" | "
";

&MAIL_FLAGS = 0;
&MAIL_TO = ASU_MCFEM_AET.MCF_EMAIL_FROM;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Support case#" | &parsedCaseId | " is not correct and does not contain numeric digits (Ex. 343567#) ";
&MAIL_TEXT = &text;
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_SENDER = "support@asu.edu";
&delim = ";";
&Content_Type = "Content-type: text/html; charset=utf8";


&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER, &delim, &Content_Type);

If Not (&RET = 0) Then
MessageBox(0, "", 0, 0, "Return status from mail = " | &RET);
End-If;

End-If;