Finding Menu Path

Here are some good SQL's to have in your library.

When a process name is known:

SELECT DISTINCT
PRCS.PRCSTYPE,
PRCS.PRCSNAME,
PRCS.DESCR,
PAGE.PNLGRPNAME as Component,
'Home > ' || RTRIM(M.MENUGROUP) || ' > ' || RTRIM(M.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as Location
FROM PSMENUDEFN M,
PSMENUITEM ITEM,
PS_PRCSDEFNPNL PAGE,
PS_PRCSDEFN PRCS
WHERE M.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PRCS.PRCSNAME = 'PY_PULL_COST'


When a component name is known:

SELECT DISTINCT PRCS.PRCSTYPE , PRCS.PRCSNAME , PRCS.DESCR ,
PAGE.PNLGRPNAME as Component , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PS_PRCSDEFNPNL PAGE , PS_PRCSDEFN PRCS
WHERE MENU.MENUNAME = ITEM.MENUNAMEAND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PAGE.PNLGRPNAME = 'component-name'

When a record name is known:

SELECT DISTINCT PFLD.RECNAME ,
PFLD.PNLNAME as Page , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP , PSPNLFIELD PFLD
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = PFLD.PNLNAME
AND PFLD.RECNAME = 'record-name'

When a page name is known:

SELECT DISTINCT COMP.PNLNAME as Page , 'Home > ' ||
RTRIM(MENU.MENUGROUP) || ' > ' || RTRIM(MENU.MENULABEL) || ' > ' ||
RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = 'page-name'

PS Listing of Payroll / HR tables

Here is a great sample SQL for capturing PS Payroll and HR tables within PS.

select a.RECNAME, decode(a.SQLTABLENAME, ' ', 'PS_' || a.recname, a.sqltablename) table_name, a.rectype, a.OBJECTOWNERID, xlat.xlatlongname, num_rows
from psrecdefn a, psxlatitem xlat, all_tables b
where a.objectownerid in ('AWFA', 'FGL', 'HBA', 'HBN', 'HEB', 'HER', 'HFSA', 'HHP', 'HHR', 'HMCF', 'HPY', 'HRAM', 'HRAT', 'HSP', 'HTC', 'HTL', 'HTLA', 'HTLI', 'HTLR', 'HTLX', 'HRAM','HRAT','HSP' )
and xlat.fieldname = 'OBJECTOWNERID'
and xlat.fieldvalue = a.OBJECTOWNERID
and xlat.effdt = (select max(xlat1.effdt) from psxlatitem xlat1
where xlat1.fieldname = xlat.fieldname
and xlat1.fieldvalue = xlat.fieldvalue)
and b.table_name = decode(a.SQLTABLENAME, ' ', 'PS_' || a.recname, a.sqltablename)
and b.NUM_ROWS > 0

Peoplesoft Tools Tables

PSST0101 blog site had started listing out the tools tables. I wanted to post this listing on my blog as well. I have also provided the link to the orginal blog.

Enjoy

Projects

PSPROJECTDEFN — Project header table
PSPROJECTITEM — Definitions in the project
Fields

PSDBFIELD — Fields in the system
PSXLATITEM — Translate Values
Records

PSRECDEFN — Record header table
PSRECFIELD — Fields in the record (subrecords not expanded)
PSRECFIELDALL — Fields in the record (subrecords expanded)
PSKEYDEFN — Indexes
Pages

(Note: Pages still have the name panels in the PeopleTools table names)

PSPNLDEFN — Page header table
PSPNLFIELD — Page controls
PSPNLHTMLAREA — Static HTML Areas on Pages
Components

(Note: Components still have the name panel group in the PeopleTools table names)

PSPNLGRPDEFN — Component header table
PSPNLGROUP — Pages in the components
Menus

PSMENUDEFN — Menu header table
PSMENUITEM — Items (components) on the menu
Security

PSCLASSDEFN — Permission List header table
PSAUTHITEM — Menu items granted security by permission lists
PSROLEDEFN — Role header table
PSROLECLASS — Permission Lists in roles
PSOPERDEFN — User ID header table
PSROLEUSER — Roles granted to users
Portal

PSPRSMDEFN — Content References and Folders
Change Control

PSCHGCTLHIST — shows history of locked definitions with project name, incident, and description
PSCHGCTLLOCK — shows definitions that are currently locked
Application Engine


PSAEAPPLDEFN — header record; 1 row per app engine
PSAEAPPLSTATE — state records assigned to app engines
PSAEAPPLTEMPTBL — temp tables assigned to app engines
PSAESECTDEFN — sections
PSAESTEPDEFN — steps
PSAESTEPMSGDEFN
PSAESTMTDEFN — actions
HTML Definitions


PSCONTDEFN — header record; last update time, etc.
PSCONTENT — stores actual text in the HTML definition

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;
&noteSubj = "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, &noteSubj, 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";
&noteSubj = "Note added by email from ";

&RET_Note = &asuCI.Addnote(&asuCI.CASE_ID, &noteSubj, 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;

SQL Quaries for Calender and dates

/* Days week calender */
SELECT INITCAP(TRIM(TO_CHAR(dat,   'month'))) || ', ' || TO_CHAR(SYSDATE,   'yyyy') MONTH,
  MAX(DECODE(TO_CHAR(dat,   'd'),   2,   TO_CHAR(dat,   'dd'))) mon,
  MAX(DECODE(TO_CHAR(dat,   'd'),   3,   TO_CHAR(dat,   'dd'))) tue,
  MAX(DECODE(TO_CHAR(dat,   'd'),   4,   TO_CHAR(dat,   'dd'))) wed,
  MAX(DECODE(TO_CHAR(dat,   'd'),   5,   TO_CHAR(dat,   'dd'))) thu,
  MAX(DECODE(TO_CHAR(dat,   'd'),   6,   TO_CHAR(dat,   'dd'))) fri,
  MAX(DECODE(TO_CHAR(dat,   'd'),   7,   TO_CHAR(dat,   'dd'))) sat,
  MAX(DECODE(TO_CHAR(dat,   'd'),   1,   TO_CHAR(dat,   'dd'))) sun
FROM
  (SELECT TRUNC(SYSDATE,    'y') + ROWNUM -1 dat,
     TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'iw') woy
   FROM user_objects
   WHERE ROWNUM <=(ADD_MONTHS(TRUNC(SYSDATE,    'y'),    12) -TRUNC(SYSDATE,    'y')))
GROUP BY TO_CHAR(dat,   'month'),
  woy,
  TO_CHAR(dat,   'mm')
  
/*First and Last day of all month of Current year*/ 
    select MON ,First_day,last_day(First_day) last_day
    from ( 
SELECT TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'MM') mon ,
      TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'DD') DAy,
      TRUNC(SYSDATE,    'y') + ROWNUM -1 First_day
   FROM user_objects
   WHERE ROWNUM <=(ADD_MONTHS(TRUNC(SYSDATE,    'y'),    12) -TRUNC(SYSDATE,    'y'))) cal
   where  DAY=01                                                                                                                                                            
/*First and Last day of all month of Current Fiscal year*/ 
    select MON ,First_day,last_day(First_day) last_day
    from ( 
SELECT TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'MM') mon ,
      TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'DD') DAy,
      TRUNC(SYSDATE,    'y') + ROWNUM -1 First_day
   FROM user_objects
   WHERE ROWNUM <=(ADD_MONTHS(TRUNC(SYSDATE,    'y'),    15) -TRUNC(SYSDATE,    'y'))
   ) cal
   where  DAY=01 and First_day >(ADD_MONTHS(TRUNC(SYSDATE,    'y'),    2) + 30)

/*Calender*/
SELECT TRUNC(SYSDATE,    'y') + ROWNUM -1 dat,
     TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'iw') woy
   FROM PS_RS_SO_LINE

/*Get a specific Month*/
Select ADD_MONTHS(TRUNC(SYSDATE,    'y'),    2) from DUAL

/*Calender ( all date of current year)*/
   SELECT TRUNC(SYSDATE,    'y') + ROWNUM -1 dat,
     TO_CHAR(TRUNC(SYSDATE,    'y') + ROWNUM -1,    'iw') woy
   FROM PS_RS_SO_LINE
   WHERE ROWNUM <=(ADD_MONTHS(TRUNC(SYSDATE,    'y'),    12) -TRUNC(SYSDATE,    'y'))

/* First day of current year*/
   Select TRUNC(SYSDATE,    'y') from DUAL

Find the Menu Navigation for PeopleSoft Components or Processes

If you're like me, there are a lot of times when you need to make changes to a particular component or perhaps you need to run a particular process. You might know the component or processess name, but you have no idea how to navigate to the particular component or process through the PeopleSoft navigation.


Here is a great tip for a method to finding the portal hierarchy path to a particular component. Let's say that we are trying to find the JOB_DATA component in HCM. Yes, I know most people know the navigation for this one. However, this is just an example, so bare with me. You can use any component name that you desire. I am using JOB_DATA for simplicity. If you have access to run a SQL, below is a piece of code that uses the CONNECT BY PRIOR command that will help you to find the navigation.


select lpad(' ', 10*level-10) || PS.PORTAL_LABEL

from PSPRSMDEFN PSstart with PS.PORTAL_URI_SEG2 = 'JOB_DATA'

and PS.PORTAL_URI_SEG3 in ('GBL','USA')

connect by prior PS.PORTAL_PRNTOBJNAME = PS.PORTAL_OBJNAME

and PS.PORTAL_NAME = 'EMPLOYEE'




This SQL gives the following results:




Job Data

Job Information

Workforce Administration

Root

CONNECT BY PRIOR is a condition that identifies the relationship between parent rows and child rows of the hierarchy. Here is the syntax.


Syntax:

SELECT...
[START WITH initial_condition]
CONNECT BY [nocycle] PRIOR recurse_condition
[ORDER SIBLINGS BY order_by_clause]




Key:

START WITH : The row(s) to be used as the root of the hierarchy

CONNECT BY : Condition that identifies the relationship between parent and child rows of the hierarchy

NOCYCLE : Do not circle around loops (where the current row has a child which is also its ancestor.)

ORDER SIBLINGS BY : Preserve ordering of the hierarchical query then apply the order_by_clause to the sibling rows

Now, let's say that you are looking for the navigation of a particular process name, you can use the SQL (slightly modified) to get the results you are looking for. Let's look for the navigation to the process named 'PSPPYRUN.'


select lpad(' ', 10*level-10) || PS.PORTAL_LABEL
from PSPRSMDEFN PS
start with PS.PORTAL_URI_SEG2 in
(select PSB.PNLGRPNAME from PS_PRCSDEFNPNL PSB where PSB.PRCSNAME = 'PSPPYRUN')
and PS.PORTAL_URI_SEG3 in ('GBL','USA')
connect by prior PS.PORTAL_PRNTOBJNAME = PS.PORTAL_OBJNAME
and PS.PORTAL_NAME = 'EMPLOYEE'




This SQL returns the following results:




Calculate Pay

Run Payroll

Payroll Processing

North American Payroll

Root

If you have a bit more time, you can create a PeopleSoft component/page where you can select the process or the component from a list and press a button to get the results in a grid. I'll leave that up to you. I hope this helps you become more efficient.







If you have any tips or other suggestions on this topic, please add your comments to this thread.

Dynamic Email URLs and Addresses

Here is the core code to a function that will dynamically generate a URL to a page in PeopleSoft (page specified by you). The email body uses a message catalog entry for formatting. The Send From email address is dynamic to the environment - so the environment that sends the email is identifiable without opening it up.

Hope this helps someone.

import PT_MCF_MAIL:MCFOutboundEmail;

Local PT_MCF_MAIL:MCFOutboundEmail &ObjectEmail = create PT_MCF_MAIL:MCFOutboundEmail();

Local Rowset &rsEmailBody;

Local string &sEmplId, &sEmailAddress, &URI, &URL, &keyList, &dbName;

/* Generate a URL for the user to access their Time Sheet */

/* Use parameters passed to function to build Key List */

&keyList = "&TIME_SHEET_ID=" | &sTimeSheetId | "&VERSION_NUM=" | &nVersionNum | "&EMPLID=" | &sEmplId | "&PERIOD_END_DT=" | &dPeriodEndDate;

/* Use GenerateComponentPortalURL function to make URL dynamic the environment you are in */

&URI = GenerateComponentPortalURL(%Portal, %Node, MenuName.ADMINISTER_EXPENSE_FUNCTIONS, %Market, Component.TE_TIME_ENTRY, "", "U");

&URL = &URI | &keyList;

/* Get data for message body */

&rsEmailBody = CreateRowset(Record.EX_TIME_AW);

&rsEmailBody.Fill("WHERE APPROVAL_OPRID = :1 AND TRANSACTION_ID = :2 AND VERSION_NUM = :3", %OperatorId, &sTimeSheetId, &nVersionNum);

/* Workflow email reply address is should be dynamic */

/* Use %DBName meta variable to capture the environment name */

&dbName = %DbName;

/*-- Initialize the usual fields of an email --*/

&ObjectEmail.From = "workflow_" | Lower(&dbName) | "@company.com";

&ObjectEmail.Recipients = &sEmailAddress;

&ObjectEmail.Subject = MsgGetText(20000, 166, "", &sTimeSheetId);

&ObjectEmail.Text = MsgGetExplainText(20000, 166, "", &sEmplId, &rsEmailBody(1).EX_TIME_AW.GetField(Field.NAME).Value, &rsEmailBody(1).EX_TIME_AW.SUBMISSION_DATE.Value, &rsEmailBody(1).EX_TIME_AW.PERIOD_END_DT.Value, &sTimeSheetId, &nVersionNum, &rsEmailBody(1).EX_TIME_AW.DTL_TOTAL.Value, &rsEmailBody(1).EX_TIME_AW.POL_TOTAL.Value, &URL);

/* Send Email */ 

Local integer &resp = &ObjectEmail.Send();

/* Verify Email was properly sent */

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 Message Set Number, message number; Or just get the formatted messages from &email.ErrorDescription, &email.ErrorDetails;*/

   

   &done = False;

   Break;

End-Evaluate;