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;

Peoplecode samples - Hide a field on Page.Activate peoplecode

I was working on a project today that required some graying of fields and hiding of fields on a page. Here are some samples:

How to gray a field? (Gray() and UnGray())

This is at scroll level 1.
Gray(Record.DERIVED_W3EB, CurrentRowNumber(), DERIVED_W3EB.LINK_PB);

How to hide a field? (Using .visible)

Local Rowset &LEVEL0, &level1, &level2;
Local Row &LEVEL0_ROW, &LEVEL1_ROW, &LEVEL2_ROW;
&LEVEL0 = GetLevel0();
&LEVEL0_ROW = &LEVEL0(1);
&level1 = &LEVEL0_ROW.GetRowset(Scroll.EMPLOYEE_REVIEW);
For &I = 1 To &level1.ActiveRowCount
&LEVEL1_ROW = &level1(&I);
&level2 = &LEVEL1_ROW.GetRowset(Scroll.REVIEW_REVIEWER);
For &J = 1 To &level2.ActiveRowCount
&LEVEL2_ROW = &level2(&J);
&Record = &LEVEL2_ROW.REVIEW_REVIEWER;
&Record.COMMENTS.visible = False;
End-For;
End-For;


Declare Function CheckMerchant PeopleCode FUNCLIB_W3EB.MERCHANTID FieldFormula;
Local Rowset &RSLocal1, &RSLevel1, &RSLEVEL1B, &RSL1BENEF;
Local Field &LinkLabelField;

Function Hide_Waived_Term_Rows(&RSLevel1 As Rowset)

For &I = &RSLevel1.ActiveRowCount To 1 Step - 1
rem Get Coverage Status;
&Coverage = &RSLevel1(&I).W3EB_SAVPLAN_VW.COVERAGE_ELECT.Value;
&TempRow = &RSLevel1(&I);
If &Coverage = "W" Or
&Coverage = "T" Or
(&RSLevel1.ActiveRowCount = 1 And
None(&RSLevel1(1).W3EB_SAVPLAN_VW.BENEFIT_PLAN.Value)) Then
&RSLevel1(&I).Visible = False;
DERIVED_W3EB.DESCR100.Visible = True;
DERIVED_W3EB.DESCR100.Value = MsgGetText(3001, 177, "Message Not Found");
DERIVED_W3EB.GRPB_LABEL.Visible = False;
DERIVED_W3EB.GRPB_LABEL1.Visible = False;
DERIVED_W3EB.GRPB_LABEL2.Visible = False;
DERIVED_W3EB.TEXT_LABEL2.Visible = False;
Else
&ret = False;
&rsPlanType = GetLevel0()(1).W3EB_SAVSUMSRCH.PLAN_TYPE.Value;
&RSLocal1 = GetLevel0()(1).GetRowset(Scroll.W3EB_SAVPLAN_VW);
&rsBenefitPlan = &RSLocal1(1).W3EB_SAVPLAN_VW.BENEFIT_PLAN.Value;
&LinkLabelField = &RSLocal1(1).DERIVED_W3EB.NAME1;

/* Check for a Knowledge provider merchant and then hide or unhide the Authoria subpanel */
&RSLocal1(1).DERIVED_W3EB.LINK2_PB.Label = &LinkLabelField.Value;
&ret = CheckMerchant(&rsPlanType, &rsBenefitPlan, "KNOWLEDGE", &METHOD, &MERCHANT_ID, &PSPOLICYURL, &PSPROVIDERSURL);

If &ret = False Then

If Len(&PSPOLICYURL) <> 0 Then
&RSLocal1(1).DERIVED_W3EB.LINK2_PB.Visible = True;
&RSLocal1(1).DERIVED_W3EB.LINK2_PB.Label = &LinkLabelField.Value;
&LinkLabelField.Visible = False;
DERIVED_W3EB.URL_ID = &PSPOLICYURL;
Else
&RSLocal1(1).DERIVED_W3EB.LINK2_PB.Visible = False;
&LinkLabelField.Visible = True;
End-If;
Else
REM AUTH_WRK.PLAN_TYPE = W3EB_GLOBAL_WRK.PLAN_TYPE;
DERIVED_W3EB.MERCHANTID = &MERCHANT_ID;
&LinkLabelField.Visible = False;
End-If;
DERIVED_W3EB.DESCR100.Visible = False;
DERIVED_W3EB.GRPB_LABEL.Visible = True;
DERIVED_W3EB.GRPB_LABEL1.Visible = True;
DERIVED_W3EB.GRPB_LABEL2.Visible = True;
DERIVED_W3EB.TEXT_LABEL2.Visible = True;
&RSL1BENEF = GetLevel0()(1).GetRowset(Scroll.W3EB_SVBENEF_VW);
&VALUE = &RSL1BENEF(1).W3EB_SVBENEF_VW.BENEF_PCT.Value;
&ARC = &RSL1BENEF.ActiveRowCount;
If &ARC <= 1 And
None(&VALUE) Then
DERIVED_W3EB.TEXT_NO_DATA.Visible = False;
DERIVED_W3EB.ERROR_TEXT.Value = MsgGetExplainText(3001, 58, "Message_not Found");
/********** Begin Resolution 666486 *********************/
Else
DERIVED_W3EB.TEXT_NO_DATA.Visible = True;
/*********** End Resolution 666486 *********************/
End-If;
End-If;
End-For;
End-Function;

Function Hide_Waived_Term_Rows2(&RSLevel1 As Rowset)


Evaluate %Page
When Page.W3EB_DISAADD_MAIN
SQLExec("SELECT %DATEOUT(A.EFFDT), A.COVERAGE_ELECT FROM PS_DISABILITY_BEN A WHERE A.EMPLID = :1 AND A.EMPL_RCD = :2 AND A.PLAN_TYPE = :3 AND A.COVERAGE_BEGIN_DT = (SELECT MAX(X.COVERAGE_BEGIN_DT) FROM PS_DISABILITY_BEN X WHERE X.EMPLID = A.EMPLID AND X.EMPL_RCD = A.EMPL_RCD AND X.PLAN_TYPE = A.PLAN_TYPE AND X.COVERAGE_BEGIN_DT <= %DATEIN(:4)) AND A.EFFDT = (SELECT MAX(Z.EFFDT) FROM PS_DISABILITY_BEN Z WHERE Z.EMPLID = A.EMPLID AND Z.EMPL_RCD = A.EMPL_RCD AND Z.PLAN_TYPE = A.PLAN_TYPE AND Z.COVERAGE_BEGIN_DT = A.COVERAGE_BEGIN_DT)", W3EB_DIS_SRCH.EMPLID, W3EB_DIS_SRCH.EMPL_RCD, W3EB_DIS_SRCH.PLAN_TYPE, DERIVED_W3EB.ASOFDATE, &EFFDT, &COVERAGE_ELECT);

If &COVERAGE_ELECT = "W" Or
&COVERAGE_ELECT = "T" Then
UnHide(DERIVED_W3EB.TEXT_LABEL2);
DERIVED_W3EB.GRPB_LABEL.Visible = False;
DERIVED_W3EB.GRPB_LABEL1.Visible = False;
End-If;
&VALUE = &RSLevel1(1).W3EB_DEPMAIN_VW.EFFDT.Value;
&I = &RSLevel1.ActiveRowCount;

If &I <= 1 And
None(&VALUE) Then
UnHide(DERIVED_W3EB.TEXT_LABEL2);
DERIVED_W3EB.GRPB_LABEL.Visible = False;
DERIVED_W3EB.GRPB_LABEL1.Visible = False;
End-If;

When Page.W3EB_LIFEADD_MAIN


SQLExec("SELECT %DATEOUT(A.EFFDT), A.COVERAGE_ELECT FROM PS_LIFE_ADD_BEN A WHERE A.EMPLID = :1 AND A.EMPL_RCD = :2 AND A.PLAN_TYPE = :3 AND A.COVERAGE_BEGIN_DT = (SELECT MAX(X.COVERAGE_BEGIN_DT) FROM PS_LIFE_ADD_BEN X WHERE X.EMPLID = A.EMPLID AND X.EMPL_RCD = A.EMPL_RCD AND X.PLAN_TYPE = A.PLAN_TYPE AND X.COVERAGE_BEGIN_DT <= %DATEIN(:4)) AND A.EFFDT = (SELECT MAX(Z.EFFDT) FROM PS_LIFE_ADD_BEN Z WHERE Z.EMPLID = A.EMPLID AND Z.EMPL_RCD = A.EMPL_RCD AND Z.PLAN_TYPE = A.PLAN_TYPE AND Z.COVERAGE_BEGIN_DT = A.COVERAGE_BEGIN_DT)", W3EB_INS_SRCH.EMPLID, W3EB_INS_SRCH.EMPL_RCD, W3EB_INS_SRCH.PLAN_TYPE, DERIVED_W3EB.ASOFDATE, &EFFDT, &COVERAGE_ELECT);

If &COVERAGE_ELECT = "W" Or
&COVERAGE_ELECT = "T" Then
UnHide(DERIVED_W3EB.TEXT_LABEL2);
DERIVED_W3EB.GRPB_LABEL.Visible = False;
DERIVED_W3EB.GRPB_LABEL1.Visible = False;
DERIVED_W3EB.GRPB_LABEL2.Visible = False;
End-If;
&VALUE = &RSLevel1(1).W3EB_BENLIFE_VW.EFFDT.Value;
&I = &RSLevel1.ActiveRowCount;
If &I <= 1 And
None(&VALUE) Then
UnHide(DERIVED_W3EB.TEXT_LABEL2);
DERIVED_W3EB.GRPB_LABEL.Visible = False;
DERIVED_W3EB.GRPB_LABEL1.Visible = False;
DERIVED_W3EB.GRPB_LABEL2.Visible = False;
End-If;
&RSLEVEL1B = GetLevel0()(1).GetRowset(Scroll.W3EB_PRIMBEN_VW);
If (&RSLEVEL1B.ActiveRowCount <= 1 And
None(&RSLEVEL1B(1).DERIVED_W3EB.COVERAGE_LVL2.Value)) Then
DERIVED_W3EB.GRPB_LABEL3.Visible = False;
DERIVED_W3EB.DESCRLONG.Visible = True;
Evaluate W3EB_INS_SRCH.PLAN_TYPE.Value
When 24
When 25
If All(&RSLEVEL1B(1).W3EB_PRIMBEN_VW.DEPENDENT_BENEF.Value) Then
DERIVED_W3EB.GRPB_LABEL3.Visible = True;
DERIVED_W3EB.DESCRLONG.Visible = True;
DERIVED_W3EB.TRANSFER_PB.Visible = False;
Else

/********* BEGIN RESOLUTION 305641 *********/
DERIVED_W3EB.DESCRLONG.Value = MsgGetText(3001, 260, "MESSAGE NOT FOUND");
/********* END RESOLUTION 305641 *********/
End-If;
When-Other
DERIVED_W3EB.DESCRLONG.Value = MsgGetExplainText(3001, 58, "Message Not Found");

End-Evaluate;
Else

DERIVED_W3EB.GRPB_LABEL3.Visible = True;
Evaluate W3EB_INS_SRCH.PLAN_TYPE.Value
When 24
When 25
DERIVED_W3EB.DESCRLONG.Visible = True;
DERIVED_W3EB.TRANSFER_PB.Visible = False;
When-Other
DERIVED_W3EB.DESCRLONG.Visible = True;
rem DERIVED_W3EB.TRANSFER_PB.Visible = True; /* the visibility is determined in Fillscroll()*/
End-Evaluate;
End-If;

Evaluate W3EB_INS_SRCH.PLAN_TYPE.Value
When 20
When 21
When 22
When 27
For &I = &RSLEVEL1B.ActiveRowCount To 1 Step - 1
If (&RSLEVEL1B.GetRow(&I).GetRecord(Record.W3EB_PRIMBEN_VW).GetField(Field.BENEF_PCT).Value = 0 And
&RSLEVEL1B.GetRow(&I).GetRecord(Record.W3EB_PRIMBEN_VW).GetField(Field.FLAT_AMOUNT).Value = 0) Then
HideRow(Record.W3EB_PRIMBEN_VW, &I);
End-If;
End-For;
End-Evaluate;

When Page.W3EB_LIFINSALLBYNM

/* The following code is used to check to see if no rows were returned after the Fill_View_Scroll Function was executed if no rows exsist, then all fields are hidden and a message is displayed */

&VALUE = &RSLevel1(1).W3EB_BENSUM_VW.EFFDT.Value;
&I = &RSLevel1.ActiveRowCount;
If &I <= 1 And /* a scroll with no 0 rows really contains 1 row with values for the keys */
None(&VALUE) Then
UnHide(DERIVED_W3EB.TEXT_LABEL2);
DERIVED_W3EB.GRPB_LABEL.Visible = False;
Else
Hide(DERIVED_W3EB.TEXT_LABEL2);
DERIVED_W3EB.GRPB_LABEL.Visible = True;
End-If;

/* creates select from the Life_add_ben table for that emplid */

&SQL = CreateSQL("SELECT A.EMPL_RCD, A.PLAN_TYPE, %DATEOUT(A.EFFDT), A.COVERAGE_ELECT FROM PS_LIFE_ADD_BEN A WHERE A.EMPLID = :1", W3EB_EMPL_SRCH.EMPLID);

/* executes SQL */
While &SQL.Fetch(&EMPL_RCD, &PLAN_TYPE, &EFFDT, &COVERAGE_ELECT);

/* checks for waived or terminated elections and hides the appropriate rows*/
If (&COVERAGE_ELECT = "W" Or
&COVERAGE_ELECT = "T") Then
For &I = &RSLevel1.ActiveRowCount To 1 Step - 1
If (&RSLevel1(&I).W3EB_BENSUM_VW.EMPL_RCD.Value = &EMPL_RCD And
&RSLevel1(&I).W3EB_BENSUM_VW.PLAN_TYPE.Value = &PLAN_TYPE And
(&RSLevel1(&I).W3EB_BENSUM_VW.EFFDT.Value < &EFFDT And
&EFFDT <= DERIVED_W3EB.ASOFDATE)) Then
&RSLevel1(&I).Visible = False;
End-If;
End-For;
End-If;
End-While;
End-Evaluate;
End-Function;

XMLP from Field Change and Email *.rtf

Here is a great sample that will invoke a XMLP and email the results to a email address within a page. We used this to send out Offer letters to faculty. My good colleague Roger and I worked on this one. Enjoy.

/*ASU_HRM0058 9/1/2008 rdavies3
Limited code to custom test page*/

/*** ASU_HRM0058 9/30/2008 J.McMahon ***/
/*** Added XMLP rowset logic, and email logic ***/


import PSXP_RPTDEFNMANAGER:*;
import PSXP_XMLGEN:*;
import PSXP_ENGINE:*;
import PT_MCF_MAIL:*;

Declare Function GetFilePathServer PeopleCode ASU_FILEPATH_WK.ECFILELISTPATH FieldFormula;


/*Create an email object by setting individual parameters*/
Local PT_MCF_MAIL:MCFOutboundEmail &eMail = create PT_MCF_MAIL:MCFOutboundEmail();
Local Rowset &ASU_NOA_Letter_Rowset, &ASU_NOA_Empl_Rowset;
Local Row &person_row;
Local Record &person_rec, &derived_pb;

/*** Fetch Value from page ***/
&Email_to_from_page = FetchValue(Record.ASU_NOA_EE_HIST, 1, ASU_NOA_EE_HIST.EMAILID);
&Fiscal_Year = FetchValue(Record.ASU_NOA_EE_HIST, 1, ASU_NOA_EE_HIST.FISCAL_YEAR);
&Emplid = FetchValue(Record.ASU_NOA_EE_HIST, 1, ASU_NOA_EE_HIST.EMPLID);
&Empl_Rcd = FetchValue(Record.ASU_NOA_EE_HIST, 1, ASU_NOA_EE_HIST.EMPL_RCD);


/*ASU_HRM0058 9/1/2008 rdavies3
NOA save and process code*/
&ASU_NOA_Empl_Rowset = GetLevel0()(1).GetRowset(Scroll.ASU_NOA_EE_HIST);
&derived_pb = GetRecord(Record.ASU_DERIVED_HR);
&person_row = &ASU_NOA_Empl_Rowset(1);
&person_rec = &person_row.GetRecord(Record.ASU_NOA_EE_HIST);
&ASU_NOA_Letter_Rowset = &person_row.GetRowset(Scroll.ASU_NOA_LTRHIST);
&person_rec.LASTUPDOPRID.Value = %UserId;
&person_rec.LAST_ACTIVITY_TIME.Value = %Datetime;

If &person_rec.EMPLID.Value = %EmployeeId Then
&person_rec.COMPLETED.Value = "Y";
&person_rec.APPROVER_OPRID.Value = %UserId;
&person_rec.APPROVEDTTM.Value = %Datetime;
&derived_pb.ASU_HR_PB_TEST_EMA.Label = "Reprint and Email";
DoSave();
CommitWork();
/* do reprint*/
Else
DoSave();
CommitWork();
End-If;



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


&sRptDefn = "ASUHRNOA";
&sTemplateId = "ASUHRNOA_1";
&sLangCode = "";
&dtAsOfDate = %Date;
&sOutputFmt = "PDF";
&RptOutputDir = &outFilePath | "HR/";


/*Set-Up Report*/
&ReportDef = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptDefn);
&ReportDef.Get();
&ReportDef.SetRuntimeDataXMLFile(&sOutputFile);

&ReportDef.OutDestination = &RptOutputDir;

/*Create Rowset*/
&rs = &ASU_NOA_Empl_Rowset;

/*Fill Rowset*/
rem &rs.FILL("WHERE FILL.FISCAL_YEAR = :1 and FILL.EMPLID = :2 and FILL.EMPL_RCD = :3", &Fiscal_Year, &Emplid, &Empl_Rcd);

/*Create Schema*/
&rds = create PSXP_XMLGEN:RowSetDS(); /*package method*/
&mySchema = &rds.GetXSDSchema(&rs);
&f1 = GetFile(&RptOutputDir | "ASU_HR_NOA.xsd", "W", %FilePath_Absolute);
&f1.WriteLine(&mySchema);
&f1.Close();


/*Create Sample XML File*/
&myXMLFile = &rds.GetXMLData(&rs, &RptOutputDir | "ASU_HR_NOA.xsd");
&f2 = GetFile(&RptOutputDir | "ASU_HR_NOA.xml", "W", %FilePath_Absolute);
&f2.WriteLine(&myXMLFile);
&f2.Close();

/* output format */
&sOutputFormat = &sOutputFmt;

/*Provide a Data Source for the Report*/
&ReportDef.SetRuntimeDataRowset(&rs);


/*Generate the Report*/
&ReportDef.ProcessReport(&sTemplateId, %Language_User, %Date, &sOutputFormat);

/*Publish the Report*/
&ReportDef.Publish("", &RptOutputDir, "XMLP", 9999999);
rem &sFileExt = GetFileExtension(&sOutputFormat);


/*Send Mail*/
rem &ToList = "Jeromy.McMahon@asu.edu";
&ToList = &Email_to_from_page;
&FromList = "support@asu.edu";
&ReplyToList = "";
&Subject = "NOA (Notice of Appointment) Letter Attached";
&eMail.Recipients = &ToList; /*comma separeted list of email addresses*/
&eMail.From = &FromList; /*from email address*/
&eMail.ReplyTo = &ReplyToList; /*in case the reply is to be sent to a different email address*/
&eMail.Subject = &Subject;

/*Body for multiple parts*/
Local string &plain_text = "Attached you will find your copy of the Notice of Appointment letter.";
Local PT_MCF_MAIL:MCFBodyPart &text = create PT_MCF_MAIL:MCFBodyPart();
&text.Text = &plain_text;

Local PT_MCF_MAIL:MCFBodyPart &attach = create PT_MCF_MAIL:MCFBodyPart();

&attach.SetAttachmentContent(&RptOutputDir | "RptInst\" | "ASUHRNOA.pdf", %FilePath_Absolute, "ASUHRNOA.pdf", "ASUHRNOA", "", "");

Local PT_MCF_MAIL:MCFMultipart &mp = create PT_MCF_MAIL:MCFMultipart();
&mp.AddBodyPart(&text);
&mp.AddBodyPart(&attach);
&eMail.MultiPart = ∓

Local integer &resp = &eMail.Send();
/*now check &resp for the result*/
Local boolean &done;
Evaluate &resp
When %ObEmail_Delivered
/*every thing ok*/
&done = True;
Break;
When %ObEmail_NotDelivered
/*check &eMail.InvalidAddresses, &eMail.ValidSentAddresses and &eMail.ValidUnsentAddresses*/
&done = False;
Break;
When %ObEmail_PartiallyDelivered
/*check &eMail.InvalidAddresses, &eMail.ValidSentAddresses and &eMail.ValidUnsentAddresses*/
&done = True;
Break;
When %ObEmail_FailedBeforeSending
/*get the formatted messages from &eMail.ErrorDescription, &eMail.ErrorDetails*/
&done = False;
Break;
End-Evaluate;

CommitWork();

Audit requests - SQL samples

I have had the pleasure of creating mass amounts of data for the State auditors this week. Here are some queries that I have coded for this data.

Note: You will need to change the plan types and dates for your own companies and institutions.

SELECT DISTINCT B.EMPLID, B.NAME, TO_CHAR(B.PAY_END_DT,'YYYY-MM-DD'), A.PLAN_TYPE, A.BENEFIT_PLAN, A.DED_CUR, A.DEDCD, A.DED_CLASS, TO_CHAR(C.EFFDT,'YYYY-MM-DD'), C.COVERAGE_ELECT, C.COVRG_CD, C.EMPLID
FROM PS_PAY_DEDUCTION A, PS_PAY_CHECK B, PS_HEALTH_BENEFIT C
WHERE A.COMPANY = B.COMPANY
AND A.PAYGROUP = B.PAYGROUP
AND A.PAY_END_DT = B.PAY_END_DT
AND A.OFF_CYCLE = B.OFF_CYCLE
AND A.PAGE_NUM = B.PAGE_NUM
AND A.LINE_NUM = B.LINE_NUM
AND A.SEPCHK = B.SEPCHK
AND A.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR
AND A.PLAN_TYPE IN ('1X','1Y','1Z','10','11') /*** Health and Dental plan types ***/
AND C.EMPLID = B.EMPLID
AND C.EMPL_RCD = B.EMPL_RCD
AND C.COVERAGE_ELECT = 'E'
AND ( C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_HEALTH_BENEFIT C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.EMPL_RCD = C_ED.EMPL_RCD
AND C.COBRA_EVENT_ID = C_ED.COBRA_EVENT_ID
AND C.PLAN_TYPE = C_ED.PLAN_TYPE
AND C.BENEFIT_NBR = C_ED.BENEFIT_NBR
AND C_ED.EFFDT <= B.PAY_END_DT))
AND B.PAY_END_DT BETWEEN TO_DATE('2007-07-01','YYYY-MM-DD') AND TO_DATE('2008-06-30','YYYY-MM-DD')
AND A.PLAN_TYPE = C.PLAN_TYPE
ORDER BY 1, 3, 4


-- By month for Audits

select distinct
A.*,B.empl_rcd, B.empl_class,B.reg_temp, B.jobcode,B.comprate,B.annual_rt, B.grade, B.sal_admin_plan
,C.HIRE_DT,TO_CHAR(((SYSDATE - C.HIRE_DT) / 365),99.99) TOTAL_YEARS_WORKED
,TO_CHAR((SYSDATE - C.HIRE_DT),999999) TOTAL_DAYS_WORKED
,D.*, E.*
FROM (PS_PAY_CHECK A LEFT OUTER JOIN PS_JOB B ON A.COMPANY = B.COMPANY AND A.PAYGROUP = B.PAYGROUP AND B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD ), PS_EMPLOYMENT C, PS_PAY_EARNINGS D, PS_PAY_OTH_EARNS E
WHERE (B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.EMPL_RCD = B_ED.EMPL_RCD
AND B_ED.EFFDT <= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.EMPL_RCD = B_ES.EMPL_RCD
AND B.EFFDT = B_ES.EFFDT)
AND B.EMPLID = C.EMPLID
AND B.EMPL_RCD = C.EMPL_RCD
AND A.COMPANY = D.COMPANY
AND A.PAYGROUP = D.PAYGROUP
AND A.PAY_END_DT = D.PAY_END_DT
AND A.OFF_CYCLE = D.OFF_CYCLE
AND A.PAGE_NUM = D.PAGE_NUM
AND A.LINE_NUM = D.LINE_NUM
AND A.SEPCHK = D.SEPCHK
AND A.COMPANY = E.COMPANY
AND A.PAYGROUP = E.PAYGROUP
AND A.PAY_END_DT = E.PAY_END_DT
AND A.OFF_CYCLE = E.OFF_CYCLE
AND A.PAGE_NUM = E.PAGE_NUM
AND A.LINE_NUM = E.LINE_NUM
AND A.SEPCHK = E.SEPCHK
AND A.CHECK_DT BETWEEN TO_DATE('2007-07-01','YYYY-MM-DD') AND TO_DATE('2007-07-31','YYYY-MM-DD'))
order by a.emplid, a.paygroup

Referral Information on TAM - Requiring referrals when applying for a position

Local Rowset &RS_Value;
&RS_Value = GetLevel0()(CurrentRowNumber()).GetRowset(Scroll.HRS_APP_PROFILE)(1).GetRowset(Scroll.HRS_AL_SRCE_I);

For &i = 1 To &RS_Value.ActiveRowCount

If &RS_Value(&i).HRS_AL_SRCE_I.HRS_SOURCE_ID.Value <> "" Or
&RS_Value(&i).HRS_AL_SRCE_I.HRS_SUBSOURCE_ID.Value <> "" Then
Error ("Please enter a referral source before continuing on.");
Else
&controller.nextPage();
&controller.hideunhideNextPrevious();
&controller.populateJobFamilies();

HRS_CECNFRM_MSG.HRS_SAVE_CONFM.Visible = False;
HRS_CECNFRM_MSG.HRS_APP_CHECK_ICN.Visible = False;
HRS_CECNFRM_MSG.HRS_APP_CHECK_ICN.Enabled = False;

End-If;

End-For;

Primary job / Peoplesoft - dealing with multiple jobs - JOB_INDICATOR

This solution is a great one to set the JOB_INDICATOR to primary. This logic is based upon max(annual_rt) and min empl_rcd if the annual_rt is the same for the jobs.

Currently, we have a AE process that will set all max current rows to N, and all future dated rows to N. Then we evaluate which jobs are primary.
Here is a sample:

/*** Update rows to N ***/

UPDATE PS_JOB A
SET A.job_indicator = 'N', A.LASTUPDDTTM = %CurrentDateIn, A.LASTUPDOPRID = 'ASU_PRIM_JOB'
WHERE A.job_indicator <> 'N'
AND A.EFFDT = (
SELECT MAX(A1.EFFDT)
FROM PS_JOB A1
WHERE A1.EMPLID = A.EMPLID
AND A1.EMPL_RCD = A.EMPL_RCD
AND A1.EFFDT<=SYSDATE)
AND A.EFFSEQ = (
SELECT MAX(A2.EFFSEQ)
FROM PS_JOB A2
WHERE A2.EMPLID = A.EMPLID
AND A2.EMPL_RCD = A.EMPL_RCD
AND A2.EFFDT = A.EFFDT)


/*** Update future dated rows ***/
UPDATE PS_JOB A
SET A.job_indicator = 'N', A.LASTUPDDTTM = %CurrentDateIn, A.LASTUPDOPRID = 'ASU_PRIM_JOB'
WHERE A.job_indicator <> 'N'
AND A.EFFDT = (
SELECT MAX(A1.EFFDT)
FROM PS_JOB A1
WHERE A1.EMPLID = A.EMPLID
AND A1.EMPL_RCD = A.EMPL_RCD
AND A1.EFFDT > SYSDATE)
AND A.EFFSEQ = (
SELECT MAX(A2.EFFSEQ)
FROM PS_JOB A2
WHERE A2.EMPLID = A.EMPLID
AND A2.EMPL_RCD = A.EMPL_RCD
AND A2.EFFDT = A.EFFDT)

/*** Insert values into temp table for processing ***/
%InsertSelect(ASU_HR0022_TBL, JOB A, PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE), EMPLID = A.EMPLID, EMPL_RCD = A.EMPL_RCD, EFFDT = A.EFFDT, EFFSEQ = A.EFFSEQ, EMPL_STATUS = A.EMPL_STATUS, FTE = A.FTE,ANNUAL_RT = A.ANNUAL_RT, JOB_INDICATOR = 'P', ASU_OLD_JOB_IND = A.JOB_INDICATOR)
FROM PS_JOB A
WHERE A.EFFDT = (
SELECT MAX(A1.EFFDT)
FROM PS_JOB A1
WHERE A1.EMPLID = A.EMPLID
AND A1.EMPL_RCD = A.EMPL_RCD
AND A1.EFFDT <= SYSDATE)
AND A.EFFSEQ = (
SELECT MAX(A2.EFFSEQ)
FROM PS_JOB A2
WHERE A2.EMPLID = A.EMPLID
AND A2.EMPL_RCD = A.EMPL_RCD
AND A2.EFFDT = A.EFFDT)
AND A.EMPL_STATUS NOT IN ('T','R')
AND A.ANNUAL_RT = (
SELECT MAX(A3.ANNUAL_RT)
FROM PS_JOB A3
WHERE A3.EMPLID = A.EMPLID
AND a3.empl_status NOT IN ('T','R')
AND a3.effdt = (
SELECT MAX(a3x.effdt)
FROM ps_job a3x
WHERE a3x.emplid = a3.emplid
AND a3x.empl_rcd = a3.empl_rcd
AND a3x.effdt <= sysdate)
AND a3.effseq = (
SELECT MAX(a3y.effseq)
FROM ps_job a3y
WHERE a3y.emplid = a3.emplid
AND a3y.empl_rcd = a3.empl_rcd
AND a3y.effdt = a3.effdt))
AND A.EMPL_RCD = (
SELECT MIN(A4.EMPL_RCD)
FROM PS_JOB A4
WHERE A4.EMPLID = A.EMPLID
AND a4.empl_status NOT IN ('T','R')
AND a4.annual_rt = a.annual_rt
AND a4.effdt = (
SELECT MAX(a4x.effdt)
FROM ps_job a4x
WHERE a4x.emplid = a4.emplid
AND a4x.empl_rcd = a4.empl_rcd
AND a4x.effdt <= sysdate)
AND a4.effseq = (
SELECT MAX(a4y.effseq)
FROM ps_job a4y
WHERE a4y.emplid = a4.emplid
AND a4y.empl_rcd = a4.empl_rcd
AND a4y.effdt = a4.effdt))

/*** Perform update ***/

UPDATE PS_JOB A
SET A.job_indicator = 'P', A.LASTUPDDTTM = %CurrentDateIn, A.LASTUPDOPRID = 'ASU_PRIM_JOB'
WHERE EXISTS (
SELECT 'X'
FROM PS_ASU_HR0022_TBL B
WHERE B.emplid = A.emplid
AND B.EMPL_RCD = A.EMPL_RCD
AND B.EFFDT = A.EFFDT
AND B.EFFSEQ = A.EFFSEQ
AND B.JOB_INDICATOR = 'P')
AND A.JOB_INDICATOR <> 'P'


/*** Peoplecode for report ***/

/*** Keep in mind, you will need to create a function for your own environment's path - J.McMahon ***/

Declare Function GetFilePathServer PeopleCode ASU_FILEPATH_WK.ECFILELISTPATH FieldFormula;

Local Record &Readrec, &RecLine, &Readrec2, &RecLine2;
Local File &MYFILE;
Local SQL &SQL2, &SQL3;
Local string &Filename;

GetFilePathServer(&outFilePath, &inFilePath, ASU_HR0022_AET.DBNAME);

If ASU_HR0022_AET.PROCESS_INSTANCE > 0 Then
If ASU_HR0022_AET.DBNAME = "ASUSAPRD" Then
&Filename = &outFilePath | "HR/" | "logs/ASU_PRIM_JOB_PRD.txt";
Else
&Filename = &outFilePath | "HR/" | "logs/ASU_PRIM_JOB.txt";
End-If;
Else
&Filename = "R:\temp\ASU_PRIM_JOB.txt";
End-If;

&MYFILE = GetFile(&Filename, "W", %FilePath_Absolute);

If &MYFILE.IsOpen Then
&MYFILE.WriteLine(" ");
&MYFILE.WriteLine(" ");
&MYFILE.WriteLine("Process Instance: " | ASU_HR0022_AET.PROCESS_INSTANCE | " - ASU_HR0022 - Primary Job Status Report");

Local number &ErrorCount;
&ErrorCount = 0;

SQLExec("select ASU_FILLER_N5 from SYSADM.PS_ASU_HR0022_VW", &ErrorCount);

&MYFILE.WriteLine(" ");
&MYFILE.WriteLine(" ------------------------------------------------------------ ");
&MYFILE.WriteLine(" ");

/* Error Report */
&MYFILE.WriteLine(&ErrorCount | " - Errors found during this run");
&MYFILE.WriteLine(" ");

If &ErrorCount > 0 Then
&MYFILE.WriteLine(" EMPLID " | Char(44) | " Count ");
&Readrec = CreateRecord(Record.ASU_HR0022_VW_T);
&SQL2 = CreateSQL("SELECT * FROM SYSADM.PS_ASU_HR0022_VW");
While &SQL2.Fetch(&Readrec)
&MYFILE.WriteLine(&Readrec.EMPLID.Value | Char(44) | &Readrec.ASU_FILLER_N5.Value);
End-While;
&MYFILE.WriteLine(" ");

End-If;


&MYFILE.WriteLine(" ------------------------------------------------------------ ");
&MYFILE.WriteLine(" ");

/* Success Report */

Local number &changedCount;
&changedCount = 0;

SQLExec("select COUNT(*) FROM PS_ASU_HR0022_TBL where process_instance = :1", ASU_HR0022_AET.PROCESS_INSTANCE, &changedCount);

&MYFILE.WriteLine(" ------------------------------------------------------------ ");
&MYFILE.WriteLine(&changedCount | " - JOB records updated during this run - (INTO Temp Table - PS_ASU_HR0022_TBL");


&MYFILE.WriteLine(" ");
&MYFILE.WriteLine("JOB DETAILS:");
&MYFILE.WriteLine(" ");

&MYFILE.WriteLine("Process Instance" | Char(44) | " EMPLID " | Char(44) | " Empl Rcd " | Char(44) | " Effdt " | Char(44) | " Effseq " | Char(44) | "Empl Status" | Char(44) | " FTE " | Char(44) | " Annual Rt " | Char(44) | " Job Indicator " | Char(44) | "Old Job Ind");
&Readrec2 = CreateRecord(Record.ASU_HR0022_TMP);
&SQL3 = CreateSQL("SELECT A.PROCESS_INSTANCE, A.EMPLID, A.EMPL_RCD, %DateOut(A.EFFDT), A.EFFSEQ, A.EMPL_STATUS, A.FTE, A.ANNUAL_RT, A.JOB_INDICATOR, A.ASU_OLD_JOB_IND FROM PS_ASU_HR0022_TBL A WHERE A.PROCESS_INSTANCE = :1", ASU_HR0022_AET.PROCESS_INSTANCE);
While &SQL3.Fetch(&Readrec2)
&MYFILE.WriteLine(&Readrec2.PROCESS_INSTANCE.Value | Char(44) | &Readrec2.EMPLID.Value | Char(44) | &Readrec2.EMPL_RCD.Value | Char(44) | &Readrec2.EFFDT.Value | Char(44) | &Readrec2.EFFSEQ.Value | Char(44) | &Readrec2.EMPL_STATUS.Value | Char(44) | &Readrec2.FTE.Value | Char(44) | &Readrec2.ANNUAL_RT.Value | Char(44) | &Readrec2.JOB_INDICATOR.Value | Char(44) | &Readrec2.ASU_OLD_JOB_IND.Value);
End-While;
End-If;

&MYFILE.Close();

Creating Audit records - AUDIT_PERS_NID (sample)

I wanted to create a post about audits and how to work with audits within Peoplesoft. There are many different types of audits you can place within peoplesoft. I am going to discuss the main two.

(Database level audits and online audits)

Sample: Need to create a audit for when users change or update SSN (National_id)

Record that will be audited: PS_PERS_NID

Audit Record created: PS_AUDIT_PERS_NID
(Make sure all keys are removed from the audit record)

Add fields: AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN (Make fields required, check auto-update for AUDIT_STAMP)










Create Database Trigger:

CREATE OR REPLACE TRIGGER PERS_NID_TR
AFTER INSERT OR UPDATE OR DELETE ON PS_PERS_NID
FOR EACH ROW
DECLARE
V_AUDIT_OPRID VARCHAR2(64);
BEGIN
DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID);
IF INSERTING
THEN
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'A',:NEW.EMPLID,:NEW.COUNTRY,:NEW.NATIONAL_ID_TYPE,:NEW.NATIONAL_ID,:NEW.SSN_KEY_FRA,:NEW.PRIMARY_NID,:NEW.TAX_REF_ID_SGP,:NEW.LASTUPDDTTM,:NEW.LASTUPDOPRID);
ELSE
IF DELETING
THEN
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'D',:OLD.EMPLID,:OLD.COUNTRY,:OLD.NATIONAL_ID_TYPE,:OLD.NATIONAL_ID,:OLD.SSN_KEY_FRA,:OLD.PRIMARY_NID,:OLD.TAX_REF_ID_SGP,:OLD.LASTUPDDTTM,:OLD.LASTUPDOPRID);
ELSE
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'K',:OLD.EMPLID,:OLD.COUNTRY,:OLD.NATIONAL_ID_TYPE,:OLD.NATIONAL_ID,:OLD.SSN_KEY_FRA,:OLD.PRIMARY_NID,:OLD.TAX_REF_ID_SGP,:OLD.LASTUPDDTTM,:OLD.LASTUPDOPRID);
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'N',:NEW.EMPLID,:NEW.COUNTRY,:NEW.NATIONAL_ID_TYPE,:NEW.NATIONAL_ID,:NEW.SSN_KEY_FRA,:NEW.PRIMARY_NID,:NEW.TAX_REF_ID_SGP,:NEW.LASTUPDDTTM,:NEW.LASTUPDOPRID);
END IF;
END IF;
END PERS_NID_TR;
/


With a online audit, you would add this audit record to the record properties of PS_PERS_NID and migrate to PRD with those options you selected.

How to hide fields within subpage / page

Here is a great sample of Page.Activate peoplecode that will hide fields within a page or subpage. You can also use a group box around a subpage to hide all the fields within that group box.

&admin = "N";
SQLExec("select 'Y' from psroleuser where roleuser = :1 and rolename = 'RoleName'", %OperatorId, &admin);
If &admin = "Y" Then
DERIVED_TL_WEEK.TL_TA_CALC_PB.Visible = True;
End-If;

/*** Within Sub Page ***/
TL_LINK_WRK.TL_TEXT_LBL5.Visible = False;
TL_LINK_WRK.TL_TEXT_LBL1.Visible = False;
TL_LINK_WRK.TL_TEXT_LBL2.Visible = False;

SQL for Savings Bonds Interface

Here is a sample of a savings bond outbound interface.
(This would be your population query within a AE step)

SELECT NVL(A.EMPLID
, ' ')
, L.CHECK_DT
, 'B'
, NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN DED.SSN ELSE (
SELECT NID2.NATIONAL_ID
FROM PS_DEP_BENEF_NID NID2
WHERE NID2.EMPLID = A.EMPLID
AND NID2.DEPENDENT_BENEF = B.BOND_OWNER_ID) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT NAM.FIRST_NAME||' '||substr(NAM.MIDDLE_NAME
, 1
,1)||' '||NAM.LAST_NAME
FROM PS_NAMES NAM
WHERE NAM.EMPLID = DED.EMPLID
AND NAM.EFFDT = (
SELECT MAX(NAM_ED.EFFDT)
FROM PS_NAMES NAM_ED
WHERE NAM.EMPLID = NAM_ED.EMPLID
AND NAM.NAME_TYPE = NAM_ED.NAME_TYPE
AND NAM_ED.EFFDT <= SYSDATE) ) ELSE (
SELECT NAM.FIRST_NAME||' '||substr(NAM.MIDDLE_NAME
, 1
,1)||' '||NAM.LAST_NAME
FROM PS_DEPENDENT_BENEF NAM
WHERE NAM.EMPLID = A.EMPLID
AND NAM.DEPENDENT_BENEF = B.BOND_OWNER_ID) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT (CASE WHEN AD.COUNTRY = 'USA' THEN '0' ELSE '1' END)
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT >= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT (CASE WHEN DEPAD.COUNTRY = 'USA' THEN '0' ELSE '1' END)
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), '0') , (CASE WHEN B.BOND_DLVY_ID IN ('EE', ' ') THEN '0' ELSE '1' END) ,
NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.ADDRESS1
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.ADDRESS1
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN DED.NAME ELSE (
SELECT NAM.NAME
FROM PS_DEPENDENT_BENEF NAM
WHERE NAM.EMPLID = A.EMPLID
AND NAM.DEPENDENT_BENEF = B.BOND_OWNER_ID) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.ADDRESS2
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.ADDRESS2
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.ADDRESS3
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.ADDRESS3
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.CITY
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.CITY
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.STATE
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.STATE
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT SUBSTR(AD.POSTAL
,1
,5)
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT SUBSTR(DEPAD.POSTAL
,1
,5)
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT SUBSTR(AD.POSTAL
,7
,10)
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT SUBSTR(DEPAD.POSTAL
,7
,10)
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , B.BOND_OTH_REG_TYPE, (CASE WHEN B.BOND_OTH_REG_TYPE = 'C' THEN 'OR'
WHEN B.BOND_OTH_REG_TYPE = 'B' THEN 'POD' ELSE ' ' END) , NVL((CASE WHEN B.BOND_OTH_REG_TYPE IN ('C', 'B')
AND B.BOND_OTH_REG_ID IN ('EE') THEN (
SELECT NVL(NAM.FIRST_NAME||' '||substr(NAM.MIDDLE_NAME
, 1
,1)||' '||NAM.LAST_NAME
, ' ')
FROM PS_NAMES NAM
, PS_ASU_DED_INTRFAC DED2
WHERE B.EMPLID = DED2.EMPLID
AND DED2.DEDCD = 'SAVBND'
AND NAM.EMPLID = B.EMPLID
AND NAM.EFFDT = (
SELECT MAX(NAM_ED.EFFDT)
FROM PS_NAMES NAM_ED
WHERE NAM.EMPLID = NAM_ED.EMPLID
AND NAM.NAME_TYPE = NAM_ED.NAME_TYPE
AND NAM_ED.EFFDT <= SYSDATE) ) WHEN B.BOND_OTH_REG_TYPE IN ('C','B')
AND B.BOND_OTH_REG_ID NOT IN ('EE')THEN (
SELECT NVL(D.FIRST_NAME||' '||substr(D.MIDDLE_NAME
, 1
,1)||' '||D.LAST_NAME
, ' ')
FROM PS_DEPENDENT_BENEF D
WHERE D.DEPENDENT_BENEF = B.BOND_OTH_REG_ID
AND B.EMPLID = D.EMPLID) END), ' ') , (CASE WHEN B.BOND_ID = '100' THEN '4' WHEN B.BOND_ID = '200' THEN '5' WHEN B.BOND_ID =
'500' THEN '6' WHEN B.BOND_ID = '1K' THEN '7' WHEN B.BOND_ID = '5K' THEN '8' WHEN B.BOND_ID = '10K' THEN '9' ELSE '0' END) ,
L.BOND_PURCH_UNITS, A.COMPANY, NVL(A.LOCATION, ' ') , NVL(DED.SSN, ' '), DED.PAY_END_DT, DED.PAY_END_DT, ' ', ' ', '9999999999'

FROM PS_ASU_DED_INTRFAC DED, PS_JOB A, PS_BOND_SPEC_DATA B, PS_BOND_LOG L
WHERE A.EFFDT = (
SELECT MAX(A1.EFFDT)
FROM PS_JOB A1
WHERE A1.EMPLID = A.EMPLID
AND A1.EMPL_RCD = A.EMPL_RCD
AND A1.EFFDT <= SYSDATE)
AND A.EFFSEQ = (
SELECT MAX(A2.EFFSEQ)
FROM PS_JOB A2
WHERE A2.EMPLID = A.EMPLID
AND A2.EMPL_RCD = A.EMPL_RCD
AND A2.EFFDT = A.EFFDT)
AND A.EMPL_STATUS IN ('A','L','P','S')
AND DED.EMPLID = A.EMPLID
AND DED.DEDCD = 'SAVBND' /* Add your dedcd here */
AND DED.DED_CLASS = 'A'
AND A.JOB_INDICATOR = 'P'
AND B.EMPLID = A.EMPLID
AND B.COMPANY = 'Your Company Here' /* Add your company here */
AND B.EFFDT = (
SELECT MAX(B1.EFFDT)
FROM PS_BOND_SPEC_DATA B1
WHERE B1.EMPLID = B.EMPLID
AND B1.COMPANY = B.COMPANY
AND B1.PRIORITY = B.PRIORITY
AND B1.EFFDT <= SYSDATE)
AND B.EMPLID = L.EMPLID
AND B.BOND_ID = L.BOND_ID
AND B.BOND_OWNER_ID = L.BOND_OWNER_ID
AND L.BOND_PURCH_UNITS >= 1
AND L.PAY_END_DT = DED.PAY_END_DT

SQL for Position Managment

This is a good sample for obtaining your current SUP and reports_to. (Based upon position)

SELECT a.emplid
, a.position_nbr
, a.reports_to
, b.name
, b.first_name
, b.last_name
, b.middle_name
, c.phone
, c.extension
FROM ps_job a
, ps_names b
, ps_personal_phone c
, ps_position_data d
WHERE a.emplid = b.emplid
AND a.empl_status NOT IN ('R','T')
AND a.hr_status = 'A'
AND a.effdt = (
SELECT MAX(a1.effdt)
FROM ps_job a1
WHERE a1.emplid = a.emplid
AND a1.empl_rcd = a.empl_rcd
AND a1.effdt <= sysdate)
AND a.effseq = (
SELECT MAX(a2.effseq )
FROM ps_job a2
WHERE a2.emplid = a.emplid
AND a2.empl_rcd = a.empl_rcd
AND a2.effdt = a.effdt )
AND a.emplid = c.emplid
AND c.phone_type = 'WORK'
AND b.name_type = 'PRI'
AND b.effdt = (
SELECT MAX(b_ed.effdt)
FROM ps_names b_ED
WHERE b_ed.emplid = b.emplid
AND b_ed.name_type = b.name_type
AND b_ed.effdt <= sysdate)
AND a.position_nbr = d.position_nbr
AND d.eff_status = 'A'
AND d.effdt = (
SELECT MAX(d_ed.effdt)
FROM ps_position_data d_ED
WHERE d_ed.position_nbr = d.position_nbr
AND d_ed.eff_status = d.eff_status
AND d_ed.effdt <= sysdate)
AND A.POSITION_NBR <> A.REPORTS_TO

XMLP / App Engine / App Package

Step by Step creating a Application Engine, that uses App Package, to create *.XML files from SQL objects.

1. Initialize State
%Select(OPRID,RUN_CNTL_ID)
SELECT OPRID
, RUN_CNTL_ID
FROM PS_PRCSRUNCNTL
WHERE OPRID=%OperatorId
AND RUN_CNTL_ID = %RunControl

2. Get Run Control parms - (If Any)
3. Get the data from a custom view, and place/insert into a TAO (temp table)

%InsertSelect(DISTINCT, ASU_TL_SB_TAO, ASU_TL_APROV_VW a, process_instance = %processinstance)
FROM %Table(ASU_TL_APROV_VW) a
WHERE a.reports_to = %Bind(REPORTS_TO)
AND a.reported_status = %Bind(REPORTED_STATUS)
AND a.dur BETWEEN %Bind(PERIOD_BEGIN_DT) AND %Bind(PERIOD_END_DT)

4. Process App Package call

import ASU_TL_APPROV_PRINT:Report;

Component ASU_TL_APPROV_PRINT:Report &oReport;

&mval = String(ASU_TL_SBL_AET.PROCESS_INSTANCE.Value);
&oReport = create ASU_TL_APPROV_PRINT:Report();
&oReport.PublishReport(&mval, ASU_TL_SBL_AET.PROCESS_INSTANCE.Value);

App Package code: Report - OnExecute

import PSXP_RPTDEFNMANAGER:*;
import PSXP_XMLGEN:*;
import PSXP_ENGINE:*;
import ASU_TL_APPROV_PRINT:Util:ReportUtil;

import %metadata:*;
import %metadata:XMLPubRptMgr:*;

class Report
property string PrintFormat get set;
property string PrintSort get set;
property string XMLOutputOption get set;
property string XMLOutputFilePath get set;


method Report();
method SQLWhereClause();
method PublishReport(&parmRptReqNbr As string, &nProcInst As number);

/*last change method PublishReport();*/
method DisplayReport(&parmRptReqNbr As string, &parmReqSeqNbr As number, &PARMREQRN As string, &TEMPLATE As string);
method PrintReport(&parmRptReqNbr As string, &parmPrinterPath As string);

private

instance Rowset &rs_Child, &rs_Main, &rs_temp, &rs_temp2, &rs_pa,;
instance string &template_use, &sRptID, &sRptReqNbr, &print_format, &print_sort, &xmlfile, &xmlfilepath, &sSQLWhereClause, &process_instance, &pa_code, &rowtype;
instance number &RN, &nReqSeqNbr, &ProcessInstance, &iCount, &a, &b, &c, &e, &g, &h, &i, &j, &k, &m, &n, &t, &req_seq_nbr, &rpt_nbr, &paCount, &numrows;
instance date &effdt;
instance XmlDoc &objXMLData;
instance XmlNode &objBaseNode, &objTermNode;
instance XmlDoc &objXSDSchema;
instance string &strFileOutput;
instance string &sProcessId, &sOutputDir, &sOutputFile;

instance ASU_TL_APPROV_PRINT:Util:ReportUtil &objReportUtil;
instance PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
instance XmlDoc &inXMLDoc;
instance string &test;
instance SQL &SQL_TA1, &SQL_TA2, &SQL_TA3, &SQL_TA4, &SQL_TA5;

method BuildDataRowset();
method GenSchema();
method CreateXMLDataFile();

end-class;

get PrintFormat
/+ Returns String +/
Return &print_format;
end-get;

set PrintFormat
/+ &NewValue as String +/
/* Default Print Format to Landscape if blank */
If &NewValue = "" Then
&print_format = "1"
Else
&print_format = &NewValue;
End-If;
end-set;

get PrintSort
/+ Returns String +/
Return &print_sort;
end-get;

set PrintSort
/+ &NewValue as String +/
end-set;

get XMLOutputOption
/+ Returns String +/
Return &xmlfile;
end-get;

set XMLOutputOption
/+ &NewValue as String +/
&xmlfile = &NewValue;
end-set;

get XMLOutputFilePath
/+ Returns String +/
Return &xmlfilepath;
end-get;

set XMLOutputFilePath
/+ &NewValue as String +/
&xmlfilepath = &NewValue;
end-set;

method Report

/* Set Report ID*/
/*CHANGE LINE
THIS IS THE REPORT NAME*/
&sRptID = "ASUTLAPV";
/* create Report Utility object */
&objReportUtil = create ASU_TL_APPROV_PRINT:Util:ReportUtil();
/*CHANGE LINE
THIS IS THE CHILD ROW SET RECORD*/
&rs_Child = CreateRowset(Record.ASU_TL_SB_TAO);

end-method;

method SQLWhereClause

end-method;

method GenSchema
Local string &sAdapter, &sDSQueryName;
Local XmlNode &objXSDSchemaBaseNode;
Local Rowset &rsResult;
Local Row &rowResult;

&rsResult = CreateRowset(Record.ASU_TL_SBSTAO);

/* Generate Schema Header XMLDoc */
&objXSDSchema = &objReportUtil.CreateSchemaHeader(&rsResult);
&objXSDSchemaBaseNode = &objXSDSchema.DocumentElement;

end-method;


/* This method will create the datasource XMLfile used by XMLP */
/* It will be stored on the app or process scheduler server */
method CreateXMLDataFile
/*CHANGE LINE (s)
THIS IS THE PARENT RECORD*/
Local Record &rec = CreateRecord(Record.ASU_TL_SBSTAO);
/*THIS IS THE CHILD RECORD*/
Local Record &recchild = CreateRecord(Record.ASU_TL_SB_TAO);

Local number &nRptNbr;
Local SQL &SQL_HDR, &sql_line;
Local boolean &svw1_exists, &svw2_exists, &term_exists;
Local string &emplid, &prev_emplid, &detailorg;

/* Setup XML file directory */
/*CHANGE LINE
THIS IS THE OUTPUT FILE NAME*/
&sOutputFile = &objReportUtil.GetOutputFile("ASU_TL_SBL.xml");
Local File &FILE = GetFile(&sOutputFile, "W", %FilePath_Absolute);
Local Record &recTemp;
/*CHANGE LINE
THIS SELECTS ALL THE HEADER ROWS*/
&SQL_HDR = CreateSQL("%SELECTALL(:1)");
/* THIS IS THE FILE LAYOUT*/
If &FILE.SetFileLayout(FileLayout.ASU_TL_APPROV_RPT) Then
/* grab all the data for top level */
&FILE.WriteLine("");
&SQL_HDR.Execute(&rec);
End-If;
While &SQL_HDR.Fetch(&rec)

&FILE.Open(&sOutputFile, "A", "UTF8", %FilePath_Absolute);
/* I use Fetch & WriteRecord because that allows me to fetch the rows one by one and not keep
the whole thing in memory as would be required using a RowSet*/
&FILE.WriteRecord(&rec);
/* grab the Level 1 child data for current top row */

/*CHANGE LINE
THIS IS THE SQL TO GET THE DATA FOR THE CHILD*/
&sql_line = CreateSQL("%selectall(:1)");


&sql_line.Execute(&recchild);
While &sql_line.Fetch(&recchild);
&FILE.WriteRecord(&recchild);
/*CHANGE LINE
THIS WRITES THE CHILD FOOTER*/
&FILE.WriteLine("");
End-While;
/*CHANGE LINE
THIS WRITES THE PARRENT FOOTER*/
&FILE.WriteLine("");



&FILE.Close();

End-While;

/* close SQL objects */
&SQL_HDR.Close();


&FILE.Open(&sOutputFile, "A", "UTF8", %FilePath_Absolute);
&FILE.WriteLine("");
&FILE.Close();



end-method;

/* Output a transcript report request to the Repor Repository or to a Printer */
method PublishReport
/+ &parmRptReqNbr as String, +/
/+ &nProcInst as Number +/

Local PSXP_XMLGEN:RowSetDS &oRowSetDSDefn;
Local File &MYFILE;
Local number &nOutDestType;
Local string &rpt_template;
try
/* create datasource rowset */
&sRptReqNbr = &parmRptReqNbr;
%This.SQLWhereClause();

/* Create XML File */
WriteToLog(%ApplicationLogFence_Level1, "CreateXMLFile Start: " | String(%Datetime));
%This.CreateXMLDataFile();
WriteToLog(%ApplicationLogFence_Level1, "CreateXMLFile End: " | String(%Datetime));
/* create the report definition */

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn("ASUTLSBL");
&oRptDefn.Get();
&oRptDefn.SetRuntimeDataXMLFile(&sOutputFile);

/* Create the PDF report if option is not XML ONLY */

If %This.XMLOutputOption <> "X" Then
/* generate the report document using the default format (PDF) on the report template */
Local string &tempFormat;
&tempFormat = &oRptDefn.GetDefaultOutputFormat();

&rpt_template = &template_use;
&oRptDefn.ProcessReport(&rpt_template, "", %Date, &tempFormat);

/* find destination (%OutDestType is only when this is called from an AE */
/* if called from from component peoplecode, the OutDestType should be given */
Local string &dir, &rrid;
Local number &pos;
&dir = GetEnv("PS_SERVDIR");
&pos = Find("prcs", &dir);

If &pos > 0 Then
/* This is being run on the process scheduler - this metavariable only available in AE */
&nOutDestType = %OutDestType;
Else
/* This is being run on the appserver - must send to Web (Report Manager) */
&nOutDestType = 6;

End-If;

/* send output to destination */
Evaluate &nOutDestType
When 3 /* Printer */
&oRptDefn.PrintOutput(%FilePath);
Break;
When 6 /* Web */
&sOutputDir = &oRptDefn.OutDestination;
SQLExec("SELECT contentid FROM PS_CDM_LIST where prcsinstance = :1", &nProcInst, &rrid);
MessageBox(0, "", 0, 0, &sRptID);
WriteToLog(%ApplicationLogFence_Error, &sRptID);

&objReportUtil.PostReport(&nProcInst, "", &sOutputDir, &rrid, "", "", Date(""), "");
Break;
End-Evaluate;

End-If;

/* Copy temporary XML file to user Output File Path */

If (%This.XMLOutputOption = "X" Or
%This.XMLOutputOption = "Y") And
%This.XMLOutputFilePath <> "" Then
&strFileOutput = &objReportUtil.writeOutputFile(RTrim(%This.XMLOutputFilePath) | "ASU_TL_SBL" | &parmRptReqNbr, ".xml");
MessageBox(0, "", 0, 0, &parmRptReqNbr);
MessageBox(0, "", 0, 0, "ABOVVE");
Else
/* Delete temporary XML file */
&objReportUtil.deleteDataXML();
End-If;


catch Exception &Err

WriteToLog(%ApplicationLogFence_Error, &Err.ToString());

end-try;

end-method;

method DisplayReport
/+ &parmRptReqNbr as String, +/
/+ &parmReqSeqNbr as Number, +/
/+ &PARMREQRN as String, +/
/+ &TEMPLATE as String +/
try
&template_use = &TEMPLATE;

/* create datasource rowset */
&sRptReqNbr = &parmRptReqNbr;
&nReqSeqNbr = &parmReqSeqNbr;

/* Create XML File */
%This.CreateXMLDataFile();

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptID);
&oRptDefn.Get();
&oRptDefn.SetRuntimeDataXMLFile(&sOutputFile);
&oRptDefn.ProcessReport(&template_use, "", %Date, "");
CommitWork();
&oRptDefn.DisplayOutput();

/* Delete temporary XML file */
&objReportUtil.deleteDataXML();


catch Exception &Err
WriteToLog(%ApplicationLogFence_Error, &Err.ToString());

end-try;
end-method;

method PrintReport
/+ &parmRptReqNbr as String, +/
/+ &parmPrinterPath as String +/
rem Local PSXP_XMLGEN:RowSetDS &oRowSetDSDefn;
Local File &MYFILE;

/* create datasource rowset */
&sRptReqNbr = &parmRptReqNbr;
%This.SQLWhereClause();
/* &rs_Main = CreateRowset(Record.SSR_TSRSLT_HDR, &rs_pa, &rs_term);
&rs_Main.Fill(&sSQLWhereClause, &sRptReqNbr);*/
/*ravi version*/
&rs_Main = CreateRowset(Record.ASU_TL_TEW_HDR);
&rs_Main.Fill();
%This.BuildDataRowset();


/* Publish Output */

/* create the report definition */
rem Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
&sRptID = "ASUTLAPV";
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptID);
&oRptDefn.Get();
&oRptDefn.SetRuntimeDataRowset(&rs_Main);

/* generate the report document using the default format (PDF) on the report template */
&oRptDefn.ProcessReport("", "", %Date, "");

/* send output to destination */
rem &oRptDefn.PrintOutput(&parmPrinterPath);
&oRptDefn.PrintOutput("\\vdbux42:cc212hp_secure");

end-method;

method BuildDataRowset

try
/* Create Rowset to be converted to XML */
For &a = 1 To &rs_Main.ActiveRowCount
&req_seq_nbr = &rs_Main(&a).SSR_TSRSLT_HDR.REQUEST_SEQ_NBR.Value;
&rpt_nbr = &rs_Main(&a).SSR_TSRSLT_HDR.REPORT_NUMBER.Value;
&rs_pa = &rs_Main(&a).GetRowset(Scroll.SSR_TSRSLT_SVW1);

&paCount = 1;
End-For
catch Exception &Err
WriteToLog(%ApplicationLogFence_Error, &Err.ToString());

end-try;
end-method;


Here is the reportUtil:


import PSXP_XMLGEN:RowSetDS;
import PSXP_RPTDEFNMANAGER:*;

class ReportUtil;

method ReportUtil();
method CreateSchemaHeader(&rsResult As Rowset) Returns XmlDoc;
method AddRowSetSchema(&rsAdd As Rowset, &objBaseNode As XmlNode) Returns XmlNode;

method CreateXMLDataHeader(&rsResult As Rowset, &nProcessInstance As number) Returns XmlDoc;
method AddRowSetXMLData(&rsAdd As Rowset, &objBaseNode As XmlNode, &nNode As number, &NumRows As number, &nProcessInstance As number) Returns XmlNode;

method GetOutputFile(&parmFileName As string) Returns string;

method getRuntimeDistribution(&processInstanceId As number, &oRptViewerArray2 As array of PSXP_RPTDEFNMANAGER:ReportViewer) Returns array of PSXP_RPTDEFNMANAGER:ReportViewer;
method PostReport(&processInstanceId As number, &sDbName As string, &outDest As string, &sReportDescr As string, &folderName As string, &serverName As string, &dExpire As date, &sburstVal As string);

method getDefaultFolderName() Returns string;
method writeOutputFile(&strFileName As string, &strFileType As string) Returns string;
method deleteDataXML();

private
method ParseStringToXMLDoc(&sXmlStringIn As string) Returns XmlDoc;
method CheckDupeRowSetElement(&objBaseNode As XmlNode) Returns XmlNode;



instance PSXP_XMLGEN:RowSetDS &objRowSetDS;

instance string &sServDir, &sDirSep, &sOutputDir, &sOutputFile;
instance array of PSXP_RPTDEFNMANAGER:ReportViewer &oRptViewerArray;
instance PostReport &Post;

end-class;

method ReportUtil /* Constructor */

&objRowSetDS = create PSXP_XMLGEN:RowSetDS();

&sServDir = GetEnv("PS_SERVDIR");
&sDirSep = "/";
If Substring(&sServDir, 1, 1) <> "/" Then
&sDirSep = "\"
End-If;

end-method;

method CreateSchemaHeader
/+ &rsResult as Rowset +/
/+ Returns XmlDoc +/
Local string &sXSDString;
Local XmlDoc &objXSDSchema;

&sXSDString = &objRowSetDS.getXSDSchema(&rsResult);
&objXSDSchema = %This.ParseStringToXMLDoc(&sXSDString);

Return &objXSDSchema;

end-method;

method AddRowSetSchema
/+ &rsAdd as Rowset, +/
/+ &objBaseNode as XmlNode +/
/+ Returns XmlNode +/
Local XmlNode &objElementNode, &objThisXSDNode;
Local string &sRowSetName, &sRowsetXsdName, &sRowsetXsdType;

&sRowSetName = &objRowSetDS.getRowSetName(&rsAdd);

&sRowsetXsdName = "rs_" | &sRowSetName;

&objElementNode = &objBaseNode.GetChildNode(4).GetChildNode(1);
&objThisXSDNode = &objElementNode.AddElement("xsd:element");
&objThisXSDNode.AddAttribute("name", &sRowsetXsdName);
&sRowsetXsdType = &sRowsetXsdName | "type";
&objThisXSDNode.AddAttribute("type", &sRowsetXsdType);

&objRowSetDS.addXSDSchema(&objBaseNode As XmlNode, &rsAdd As Rowset);

/* Check for duplicate simpleType coming from &rsAdd */
&objBaseNode = %This.CheckDupeRowSetElement(&objBaseNode);

Return &objBaseNode;

end-method;


method CreateXMLDataHeader
/+ &rsResult as Rowset, +/
/+ &nProcessInstance as Number +/
/+ Returns XmlDoc +/
Local string &sXMLString;
Local XmlDoc &objXMLData;
/*
If &nProcessInstance = 0 Then
&rsResult = %This.AddSampleData(&rsResult);
End-If;
*/
&sXMLString = &objRowSetDS.getXMLData(&rsResult, "");
&objXMLData = %This.ParseStringToXMLDoc(&sXMLString);

Return &objXMLData;

end-method;

method ParseStringToXMLDoc
/+ &sXmlStringIn as String +/
/+ Returns XmlDoc +/

Local XmlDoc &objXMLDocOut;
Local boolean &booReturn;

&objXMLDocOut = CreateXmlDoc("");

&booReturn = &objXMLDocOut.ParseXmlString(&sXmlStringIn);
If &booReturn Then
Return &objXMLDocOut;

rem Else;
rem throw create SCC_COMMGEN:Util:CommGenException(14015, 999, "999 Error creating XMLDoc" | "", "", "", "", "", "");
End-If;
end-method;

method AddRowSetXMLData
/+ &rsAdd as Rowset, +/
/+ &objBaseNode as XmlNode, +/
/+ &nNode as Number, +/
/+ &NumRows as Number, +/
/+ &nProcessInstance as Number +/
/+ Returns XmlNode +/
Local XmlNode &objThisXMLNode;
Local string &sRowSetName, &sRowsetXsdName;
Local number &nRowCount;

&sRowSetName = &objRowSetDS.getRowSetName(&rsAdd);

&sRowsetXsdName = "rs_" | &sRowSetName;

&objThisXMLNode = &objBaseNode.GetChildNode(&nNode).AddElement(&sRowsetXsdName);
&objThisXMLNode.AddAttribute("rowsetname", &sRowSetName);
&objThisXMLNode.AddAttribute("numrows", String(&NumRows));

/*
If &nProcessInstance = 0 Then
&rsAdd = %This.AddSampleData(&rsAdd);
End-If;
*/
&objRowSetDS.addXMLData(&objThisXMLNode As XmlNode, &rsAdd As Rowset);

/* If 1 return the current node passed in else return the child node */
/*
If &nOption = 1 Then
Return &objBaseNode;
Else
Return &objThisXMLNode;
End-If;
*/
Return &objBaseNode;
rem Return &objThisXMLNode;
end-method;


method CheckDupeRowSetElement
/+ &objBaseNode as XmlNode +/
/+ Returns XmlNode +/
Local array of XmlNode &arrBaseElementList;
Local XmlNode &removedNode;
Local string &sAttributeValue1, &sAttributeValue2;
Local number &i, &j;

&arrBaseElementList = &objBaseNode.GetElementsByTagName("xsd:simpleType");

For &i = 1 To &arrBaseElementList.Len
&sAttributeValue1 = &arrBaseElementList [&i].GetAttributeValue("name");
For &j = &i + 1 To &arrBaseElementList.Len
&sAttributeValue2 = &arrBaseElementList [&j].GetAttributeValue("name");
If &sAttributeValue1 = &sAttributeValue2 Then
&removedNode = &objBaseNode.RemoveChildNode(&arrBaseElementList [&j]);
End-If;
End-For;
End-For;

Return &objBaseNode;
end-method;

method GetOutputFile
/+ &parmFileName as String +/
/+ Returns String +/

Local string &sProcessId;
/* Create a file to hold the XML data */
&sProcessId = UuidGen();

CreateDirectory(&sServDir | "/files/XMLP/" | &sProcessId, %FilePath_Absolute);
CreateDirectory(&sServDir | "/files/XMLP/" | &sProcessId | "/XMLData", %FilePath_Absolute);
&sOutputDir = &sServDir | &sDirSep | "files" | &sDirSep | "XMLP" | &sDirSep | &sProcessId;
&sOutputFile = &sOutputDir | &sDirSep | "XMLData" | &sDirSep | &parmFileName;

Return &sOutputFile
end-method;


method getDefaultFolderName
/+ Returns String +/
Local string &foldername;

/* mdu ICE 1520060000 */
SQLExec("SELECT PSRF_FOLDER_NAME FROM PSRF_FLIST_TBL WHERE PSRF_IS_DEFAULT = 'Y'", &foldername);


Return &foldername;
end-method;

method PostReport
/+ &processInstanceId as Number, +/
/+ &sDbName as String, +/
/+ &outDest as String, +/
/+ &sReportDescr as String, +/
/+ &folderName as String, +/
/+ &serverName as String, +/
/+ &dExpire as Date, +/
/+ &sburstVal as String +/

rem Local PostReport &Post;
Local string &sUserType, &sUserId, &distNode, &os, &sFolderName, &sServerName, &sOutDestFormat;
Local boolean &bRet;
Local integer &iViewer;
Local number &RptInstance;
Local string &sDistNodeURL, &sFileName, &sRptURL;


WriteToLog(%ApplicationLogFence_Level1, "PostReportDistribute : Process Instance : " | &processInstanceId | " DBName : " | &sDbName | "Destination: " | &outDest | " ReportDescr : " | &sReportDescr);

/* create postReportObject */
&Post = SetPostReport();
&Post.ProcessName = "XMLP"; /* do not change. used by Report Search view */
&Post.ProcessType = "XML Publisher";

&outDest = &outDest | "/RptInst";
&outDest = Substitute(&outDest, "/", &sDirSep);
&Post.SourceReportPath = &outDest;

/*
If (&sburstVal <> "") Then
&sburstVal = " [" | &sburstVal | "] ";
Else
&sburstVal = " ";
End-If;
*/

rem &Post.ReportDescr = %This.ID | &sburstVal | &sReportDescr;
&Post.ReportDescr = &sReportDescr;

If (&processInstanceId <> 0) Then
/* get servername, foldername from scheduler request table */
SQLExec("SELECT PSRF_FOLDER_NAME, SERVERNAMERUN FROM PSPRCSRQST WHERE PRCSINSTANCE = :1", &processInstanceId, &sFolderName, &sServerName);
Else
/* get servername from report node */;
SQLExec("SELECT DISTNODENAME, OPSYS from PS_CDM_DIST_NODE", &distNode, &os);
/* WinMessage(&distNode);*/
SQLExec("SELECT SERVERNAME from PS_SERVERDEFN WHERE DISTNODENAME = :1 and OPSYS = :2", &distNode, &os, &sServerName);
End-If;

/* if method parameters exist for folder and server, otherwise use default values */
If &folderName = "" Then
&folderName = &sFolderName;
End-If;
If &folderName = "" Then
&folderName = %This.getDefaultFolderName();
End-If;
&Post.ReportFolder = &folderName;

If &serverName = "" Then
&serverName = &sServerName;
End-If;
&Post.ServerName = &serverName;

If All(&dExpire) Then
&Post.ExpirationDate = &dExpire;
Else
/* &Post.ExpirationDate = %DATE;*/
End-If;

&sOutDestFormat = "PDF";
&Post.OutDestFormat = &sOutDestFormat;
WriteToLog(%ApplicationLogFence_Level1, "The value is" | &processInstanceId);
If (&processInstanceId <> 0) Then
WriteToLog(%ApplicationLogFence_Level1, "Posting");
&Post.ProcessInstance = &processInstanceId;
End-If;

/* set distribution options */
Local string &sDistIdType_User, &sDistIdType_Role;
&oRptViewerArray = %This.getRuntimeDistribution(&processInstanceId, &oRptViewerArray);

If &oRptViewerArray <> Null Then
For &iViewer = 1 To &oRptViewerArray.Len
&sUserType = &oRptViewerArray [&iViewer].Type;
&sUserId = &oRptViewerArray [&iViewer].ID;
If (&sUserType = "2") Then
&sUserType = &sDistIdType_User;
End-If;
If (&sUserType = "3") Then
&sUserType = &sDistIdType_Role;
End-If;
WriteToLog(%ApplicationLogFence_Level1, "PostReportDistribute : usertype " | &sUserType | " userid " | &sUserId);
&Post.AddDistributionOption(&sUserType, &sUserId);
End-For;
Else
&Post.AddDistributionOption("2", %UserId);
REM &Post.AddDistributionOption("User", "PS");
rem WriteToLog(%ApplicationLogFence_Warning, "*** Warning! Report access is not set.");
End-If;

/* do post */
&Post.Put();

&RptInstance = &Post.ReportId;
&processInstanceId = &Post.ProcessInstance;


If (&RptInstance > 0) Then
MessageBox(0, "", 63, 119, "Successfully processed request with Rpt. ID %1 for Process %2 to post from directory %3", &RptInstance, &Post.ProcessName, &Post.SourceReportPath);
Else
MessageBox(0, "", 63, 122, "Not successful for process request for Process %1 to post from directory %2", &Post.ProcessName, &Post.SourceReportPath);
End-If;

If &RptInstance > 0 Then
WriteToLog(%ApplicationLogFence_Level1, "Publish : Report Instance : " | &RptInstance | " Process Instance : " | &processInstanceId);

/* formulate the report file url */
/* cannot be directly obtained from database (PS_CDM_LIST) since commit is not done yet.*/

<*
/*get distribution node url */
SQLExec("select C.URL from PSPRCSRQST A, PS_SERVERDEFN B, PS_CDM_DIST_NODE C WHERE A.SERVERNAMERUN = B.SERVERNAME AND B.DISTNODENAME = C.DISTNODENAME", &sDistNodeURL);
If %This.UseBurstValueAsOutputFileName Then
&sFileName = &sburstVal;
Else
&sFileName = &ID;
End-If;
*>
&sFileName = &sReportDescr;
&sFileName = &sFileName | "." | Lower(&sOutDestFormat);

&sRptURL = &sDistNodeURL | "/" | &RptInstance | "/" | &sFileName;
WriteToLog(%ApplicationLogFence_Level1, "distnode URL = " | &sDistNodeURL | " report URL : " | &sRptURL);
/* WinMessage("distnode URL = " | &sDistNodeURL | " report URL : " | &sRptURL);*/

Else
WriteToLog(%ApplicationLogFence_Error, "*** Warning! Report could not be posted");
End-If;

end-method;


method getRuntimeDistribution
/+ &processInstanceId as Number, +/
/+ &oRptViewerArray2 as Array of PSXP_RPTDEFNMANAGER:ReportViewer +/
/+ Returns Array of PSXP_RPTDEFNMANAGER:ReportViewer +/
Local PSXP_RPTDEFNMANAGER:ReportViewer &oRptViewer;
Local SQL &sqlDist;
Local string &distIdType, &distId, &sUserType;

&sqlDist = CreateSQL("SELECT DISTIDTYPE, DISTID FROM PS_PRCSRQSTDIST WHERE PRCSINSTANCE = :1", &processInstanceId);
While &sqlDist.Fetch(&distIdType, &distId);
If &distIdType = "2" Then
&sUserType = "USER";
Else
&sUserType = "ROLE";
End-If;
&oRptViewer = create PSXP_RPTDEFNMANAGER:ReportViewer(&sUserType, &distId);
WriteToLog(%ApplicationLogFence_Level1, "Distribution : IdType = " | &sUserType | " Id = " | &distId);

If (&oRptViewerArray2 = Null) Or
(&oRptViewerArray2.Len = 0) Then
&oRptViewerArray2 = CreateArray(&oRptViewer);
Else
&oRptViewerArray2.Push(&oRptViewer);
End-If;

End-While;
&sqlDist.Close();

Return &oRptViewerArray2;

end-method;

method writeOutputFile
/+ &strFileName as String, +/
/+ &strFileType as String +/
/+ Returns String +/
Local File &oFile, &oOutputDataXMLFile;
Local string &sFilePath, &sInput;

&sFilePath = &strFileName | &strFileType;
&oFile = GetFile(&sOutputFile, "R", %FilePath_Absolute);
If &oFile.IsOpen Then
&oOutputDataXMLFile = GetFile(&sFilePath, "W", "UTF8", %FilePath_Absolute);
If &oOutputDataXMLFile.IsOpen Then
&sInput = &oFile.GetString( True);
&oOutputDataXMLFile.WriteLine(&sInput);
&oOutputDataXMLFile.Close();
End-If;
&oFile.Close();
End-If;

Return &sFilePath;
end-method;

method deleteDataXML

Local string &sOutDest, &sXmlFile;
Local integer &i;
Local File &oFile;

&oFile = GetFile(&sOutputFile, "R", %FilePath_Absolute);
If &oFile.IsOpen Then
&oFile.Delete();
WriteToLog(%ApplicationLogFence_Level1, "Deleted Data file: " | &sOutputFile);
End-If;
RemoveDirectory(&sOutputDir, %FilePath_Absolute + %Remove_Subtree);
end-method;


5. You have to create your *.rtf template and place all the Data Source (XML File) within XMLP.
6. Don't forget the file layout within your AE.
7. Reports will show up within XMLP Report Manager