PeopleSoft Query Related Content Service

Just the other night I was speaking to another PeopleSoft developer about embedding a PeopleSoft Query into a transaction and displaying the results in modal window.  My first thought was that you should be able to do this with minimal code, but I thought it would make a good blog posting.  So I started researching options to make this happen and was able to accomplish it without any code.

For my example I am going to use a security example where you want to execute a query that has both the Operator Id and the Operator Description in a query. I know that the system is delivered with a query that gives you the id(s), but not the description and often I want that piece of information.

So first I created a query:

Second create the Related Content Service:

Third associate the Service with your Content Reference:

Test:

Filtering Related Actions in the Company Directory

With 9.2 Oracle delivers over 500 related actions in the company directory.  The problem with these related actions is they point to both MSS and ESS transactions and there are no filters that prevent these from showing up on associates within the directory.  For example, this is what the related actions looks like for myself when I look up at my manager's related actions  Why would I be able to see View Compensation History for her?  Note, that there are two View Compensation History links.  One takes you to ESS and to MSS.

We need to filter out these services.  So I created a simple filter that looks like this for ESS:

 method linkVisible
   /+ Returns Boolean +/
   /+ Extends/implements PT_RCF:ServiceFilter.linkVisible +/
   Local string &emplid;
   
   try
      &emplid = HR_OC_NODE_WRK.EMPLID;
      
   catch Exception &trash
      &emplid = DERIVED_HRCD_MN.EMPLID;
   end-try;
   
   If &emplid = %EmployeeId Then
      Return True;
   Else
      Return False;
   End-If;
end-method; 

I created another one for MSS checks to see if the current user is a manager for the target emplid.

Then I used SQL to update the backend table and moved it to QA:

 
UPDATE  PSPTCSSRVCONF A SET PACKAGEROOT = 'ZZ_RLC_SERVICEFILTERS', QUALIFYPATH = ':',
APPCLASSID = 'isMeMss' where portal_objname = 'HC_HRCD_CO_DIRECTORY_GBL' AND PNLNAME = 'HRCD_MAIN'
AND exists ( select 'x' from PSPTCSSRVDEFN x where x.PTCS_SERVICEID = a.PTCS_SERVICEID 
AND X.PORTAL_MENUNAME IN (  'ROLE_MANAGER' ));

 
UPDATE  PSPTCSSRVCONF A SET PACKAGEROOT = 'ZZ_RLC_SERVICEFILTERS', QUALIFYPATH = ':',
APPCLASSID = 'isMeEss' where portal_objname = 'HC_HRCD_CO_DIRECTORY_GBL' 
AND PNLNAME = 'HRCD_MAIN'AND exists ( select 'x' from PSPTCSSRVDEFN x 
where x.PTCS_SERVICEID = a.PTCS_SERVICEID AND X.PORTAL_MENUNAME IN (  'ROLE_EMPLOYEE' ));

Now that looks correct.

PeopleSoft Exit Interview

Been really busy lately supporting our newly upgraded HCM and Interaction Hub environments on top of trying to get all the ACA updates from Oracle.   All of this work has kept me from doing any blog post, however, the other day I was talking to my one of my clients and friends that works in our HR systems team and he was telling me about how we do exit interviews in Survey Monkey.  But the major issue with the survey was the lack of accurate demographic data that is collected by the Survey.

Right away I thought that we might be able to use Related Actions to link the demographic data from PeopleSoft to feed the Survey Monkey Exit Interview.  My first step was to look into Survey Monkey and see if I could pass the data in a query string, and their documentation pointed to Custom Variables.

The use of Custom variable allows you to pass data to the survey and this would eliminate the interviewer from having to collect this information.  I would only need to figure out how to tie the survey to the employee.  The logical choice was to use related actions to tie the survey to an employee.  So I created the following related content service and tied it to the Job Data Component using the emplid key to pass to my service.

As you can see I used an iScript to collect the data and redirect the request to the Survey Monkey URL.

   
   rem other code not shown selected most data from ps_employees;
   &queryString = "?EMPLID=" | &emplid;
   &queryString = &queryString | "&FIRST_NAME=" | &fname;
   &queryString = &queryString | "&LAST_NAME=" | &lname;
   &queryString = &queryString | "&FULLNAME=" | &utility.getUserNamebyEmplid(&emplid);
   &queryString = &queryString | "&GENDER=" | &gender;
   &queryString = &queryString | "&Ethnicity=" | ðnicDescr;
   &queryString = &queryString | "&VETERAN=" | &vetDescr;
   &queryString = &queryString | "&Manager=" | &utility.getUserNamebyEmplid(&mId);
   &queryString = &queryString | "&Region=" | ®ion;
   &queryString = &queryString | "&LOB=" | Substitute(&lobDescr, "&", "%26");
   &queryString = &queryString | "&Title=" | &title;
   &queryString = &queryString | "&JOBCODE=" | &jobcode;
   &queryString = &queryString | "&KEY_CORE=" | &KEY_CORE;
   &queryString = &queryString | "&LOBEC=" | &utility.getUserNamebyEmplid(&lobec);
   &queryString = &queryString | "&Regional=" | ®MgrName;
   &queryString = &queryString | "&Tenure=" | &tenure;
   &queryString = &queryString | "&Date=" | %Date;
   &queryString = &queryString | "&Interviewer=" | &utility.getUserNamebyOprid(%UserId);
   If %DbName = "HCMPRD" Then
      &url = GetURL(URL.ZZ_EXIT_INTERVIEW) | &queryString;
   Else
      &url = GetURL(URL.ZZ_EXIT_INTERVIEW_TEST) | &queryString;
   End-If;
   %Response.RedirectURL(&url);

Now when the Interviewer goes to Job Data they have a related Action to perform an exit interview.  This will open a new window with the exit interview in the targetcontent frame pre populated with the demographic data needed to analyze turnover.

Tools Bug overlays Related Content Menu!

We recently upgraded from 9.1 tools 8.53 to 9.2 tools 8.54 and were the victim of an interesting issue with related content and workcenters.  When you have a workcenter componet that uses the Related Content configuration, the menu was being placed underneath a div that built by the workcenter and Oracle stated that this will not be fixed until tools 8.55.  See illustration below:

So what can you do in the mean time?

After much thought I decided to use Branding Objects to see if I could inject some JavaScript to move the Related Content menu down to the component Pagebar.  To accomplish this I had already implemented Jim Marion's RequireJS using Branding System Options.  Now I all had to do was write the JavaScript to move the menu from underneath the workcenter div to the pagebar and upload it to the system.  Below is my JavaScript:  

require(['jquery'], function ($) {   

$( document ).ready(function() {



var checkExist = setInterval(function() {

   if ($("a[name^='EOTL_SS_HDR_ACTION_RT#NOTIFY']").length) {

    

      clearInterval(checkExist);

      $('<span name="zzSEP2" id="SEP" class="PSTEXT" style="margin-left:8px;margin-right:8px">|</span>').insertAfter($("a[name^='EOTL_SS_HDR_ACTION_RT#NOTIFY']"));

$('#rcMenuOnTC').insertAfter($("span[name^='zzSEP2']"));     

   }

}, 100);

});

});

After testing the JavaScript using firebug, I used the Branding Components in tools 8.54+ to upload my JavaScript for use in the Component branding.  Component Branding can be located using

 Main Menu>>PeopleTools>>Portal>>Branding>>Component Branding, just select the component that you want to inject your JavaScript into and test.

IMPROVING THE USER EXPERIENCE USING RELATED CONTENT

On June 24th, I had the distinct pleasure of being selected to present my UX presentation for the PeopleTools Tech SIG's Open Mic Call.  And when I say "selected",  I really mean I hounded a couple of the officers until the gave in and let me present it.  But nonetheless, I got the June time slot and if you missed it and have a quest membership you can watch the recorded webinar using the link below.

http://www.questdirect.org/content/view.do?contentId=2776328

Presentation Includes:

1. The use of the RLC_QueryDataSource which allows the user to utilize Pagelet Wizard to present up to 25,000 rows of data in Related Content and presents links within the Pagelet Wizard content in a user friendly lightbox API (ColorBox)
2. A demonstration on how to leverage the Query API to generate Query Spreadsheets directly to the user using Related Content.
3. A demonstration on how to generate Spreadsheet reports from related content using David Vandiver's SQR2XLS IScript code in combination with Kevin’s ROWSET_TO_CELLS function to easily generate spreadsheets.
4.  Discuss how UMB is using Related Content to generate Spreadsheets for use with the Excel to CI utility to manage Position Data.

Presentation will include:

1. The use of the RLC_QueryDataSource which allows the user to utilize Pagelet Wizard to present up to 25,000 rows of data in Related Content and presents links within the Pagelet Wizard content in a user friendly lightbox API (ColorBox)
2. A demonstration on how to leverage the Query API to generate Query Spreadsheets directly to the user using Related Content.
3. A demonstration on how to generate Spreadsheet reports from related content using David Vandiver's SQR2XLS IScript code in combination with Kevin’s ROWSET_TO_CELLS function to easily generate spreadsheets.
4. Discuss how UMB is using Related Content to generate Spreadsheets for use with the Excel to CI utility to manage Position Data.

- See more at: http://www.questdirect.org/content/view.do?contentId=2776328#sthash.ZrhJzEFJ.dpuf

Presentation will include:

1. The use of the RLC_QueryDataSource which allows the user to utilize Pagelet Wizard to present up to 25,000 rows of data in Related Content and presents links within the Pagelet Wizard content in a user friendly lightbox API (ColorBox)
2. A demonstration on how to leverage the Query API to generate Query Spreadsheets directly to the user using Related Content.
3. A demonstration on how to generate Spreadsheet reports from related content using David Vandiver's SQR2XLS IScript code in combination with Kevin’s ROWSET_TO_CELLS function to easily generate spreadsheets.
4. Discuss how UMB is using Related Content to generate Spreadsheets for use with the Excel to CI utility to manage Position Data.

- See more at: http://www.questdirect.org/content/view.do?contentId=2776328#sthash.ZrhJzEFJ.dpuf

Presentation will include:

1. The use of the RLC_QueryDataSource which allows the user to utilize Pagelet Wizard to present up to 25,000 rows of data in Related Content and presents links within the Pagelet Wizard content in a user friendly lightbox API (ColorBox)
2. A demonstration on how to leverage the Query API to generate Query Spreadsheets directly to the user using Related Content.
3. A demonstration on how to generate Spreadsheet reports from related content using David Vandiver's SQR2XLS IScript code in combination with Kevin’s ROWSET_TO_CELLS function to easily generate spreadsheets.
4. Discuss how UMB is using Related Content to generate Spreadsheets for use with the Excel to CI utility to manage Position Data.

- See more at: http://www.questdirect.org/content/view.do?contentId=2776328#sthash.ZrhJzEFJ.dpuf

Clean up Company Directory Trees

If you are utilizing the company directory then you are probably building a new company directory tree every morning for the current day's use.  Eventually you are going to need to delete these old trees or it will have a performance effect on your directory or your SES search index for person.  This is the code from the one step Application Engine that I wrote to delete those old trees that have served their purpose.  It finds the max effdt tree and then deletes any historical trees that were created three days before that date.  Please note, if you are building any future dated trees, you will need to modify this code to prevent you from deleting the current effective dated tree.

&Session = %Session;

&MYTREE = &Session.GetTree();

Local Rowset &TREE = CreateRowset(Record.PSTREEDEFN);

SQLExec("SELECT MAX(EFFDT) -3 FROM PSTREEDEFN WHERE TREE_NAME = 'COMPANY_DIRECTORY'", &Max);

&count = &TREE.Fill("Where FILL.effdt < :1 and FILL.TREE_NAME = 'COMPANY_DIRECTORY'", &Max);

For &X = 1 To &count
   If All(&MYTREE) Then
      &RETVALUE = &MYTREE.Delete("", "", "COMPANY_DIRECTORY", &TREE(&X).PSTREEDEFN.EFFDT.Value, "");
   End-If;
End-For;

Get the RLC_QueryDataSource and More

If you have been following my blog, then you know I have been showing ways your organization can improve the UX by using Related Content.  This post is dedicated to sharing that knowledge in the form of a downloadable project.  This project contains the source code for the RLC_QueryDataSource and also contains the source code for using the Query API to generate Excel documents in Related Content.  You will still need to setup your datasource and configure the related content in order to use these examples, but don't fret I show you how do that in the original posts.

https://app.box.com/s/2lc5c2ujlfsfvsp5y4s115cvzhf4wzan

RLC_QueryDataSouce

Query API in From Related Content