SQL to Identify When Job Data Field was Changed

Often in PeopleSoft we are trying to determine when an event became effective.  This SQL below is to identify when an employee's Company changed, but could be easily modified to identify when a department, position, supervisor or any job data element changed from the previous row.

SELECT NEW.EMPLID
, NEW.EFFDT
, NEW.COMPANY
, OLD.COMPANY
, OLD.HR_STATUS 
FROM PS_JOB NEW, PS_JOB OLD 
WHERE NEW.EMPLID = OLD.EMPLID 
AND NEW.EMPL_RCD = OLD.EMPL_RCD
AND NEW.COMPANY <> OLD.COMPANY 
AND OLD.EFFDT = (SELECT MAX(O_ED.EFFDT) 
                 FROM PS_JOB O_ED 
                 WHERE O_ED.EMPLID = OLD.EMPLID 
                 AND O_ED.EMPL_RCD = OLD.EMPL_RCD
                 AND O_ED.EFFDT <= (NEW.EFFDT -1))

Please note that this SQL does not use effective sequence, The reason for this is that I am not concerned with what sequence the company changed, I am only concerned about identifying the date.

Using jQuery to control Save of Related Content

Related Content allows you to add functionality as well enrich a transaction with supporting information.  In this example I will be demonstrating saving the related content transaction from the target, in the video above the target transaction is the Performance document for Brian Drennon and the related content is the quarterly comments.

TargetContent JavaScript:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script> 
<script type="text/javascript"> 
 
function save_rlcArea()
 {
   try {
window.top.document.getElementById('RelatedContent').contentWindow.document.getElementById('RCArea').contentWindow.remoteSave();}
catch (e) {
        // do nothing
  }
}
   
$( document ).ready(function() {
     
$("input[name='EXIT_SAVE#SAVE']").click(save_rlcArea());

});
</script>

This JavaScript injects jQuery into the TargetContent and creates a function called save_rlcArea.  This function attempts to call a function that is declared in the Related Content frame called remoteSave().  I use a try and catch because we are not sure if the user has even attempted to open the related content, so the frame may not even exists.

Related Content JavaScript:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script type="text/javascript">
function remoteSave() {

  submitAction_%formname(document.%formname, '#ICSave');

};
</script>
<script>
$( document ).ready(function() {

var tgtHead = window.top.document.getElementsByTagName('Head');

var scriptJQ = document.createElement('script');
scriptJQ.type='text/javascript';
scriptJQ.src = "https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js";
tgtHead[0].appendChild(scriptJQ);
scriptJQ.onload = function () {

     window.top.$("a[role='tab']").click(function() {
     try {
           window.top.document.getElementById('RelatedContent').contentWindow.document.getElementById('RCArea').contentWindow.remoteSave();}

catch (e) {

             // do nothing
         }
});
};

//inserting button if the button is not there

var tgtFrame = window.top.document.getElementById('ptifrmtgtframe');
if ( !(tgtFrame.contentWindow.$( "input[name='EXIT_SAVE\\#SAVE']" ).length )) {

var $mySep = $("<span>" , {id: "Sep", class: "PSTEXT", style: "margin-left:2px;margin-right:0px"});
var $myAnchor = $("<a>", {id: "Left", class: "PSPUSHBUTTON", role : "presentation"});
var $myAnchorSpan = $("<span>");
var $myAnchorSpanInput = $("<input>", {type: "button",name: "EXIT_SAVE#SAVE", class: "PSPUSHBUTTON", style: "min-width:62px;", value: "Save", onclick: "javascript:save_rlcArea()"});



$myAnchorSpan.append($myAnchorSpanInput);
$myAnchor.append($myAnchorSpan);



tgtFrame.contentWindow.$("#EXIT_SAVE").append($mySep);
tgtFrame.contentWindow.$("#EXIT_SAVE").append($myAnchor);
}
//end insert of button code
});
</script>

If we take a closer look at this JavaScript it is doing that majority of the work. It creates the remoteSave() function used by the target content JavaScript.  Then it loads jQuery into the top window and then adds a handler to the related content tabs, this handler saves the contents of the related area if the user uses the tabs to navigate to another related content. And finally it evaluates if the target content has the save button, if it does not have the save button then it creates the save button that will call the remoteSave() function if it is clicked.


Please note, this code is not production ready.  This is simply an example of how you can improve the users experience.  Remove references to external files if you want to use this example in production.

Embedded Popup Help for Column Heading in Grid

Recently I was asked how to add popup help to a column header in a grid.  My approach was to use the Embedded Popup Help that you can configure on the grid properties.

This embedded popup help will add an image to the grid header, for most people this should be enough help to assist the user on what actions to perform.

However, if your super users insist on having this help on the label for the specific column then just use jQuery to move the help icon.

var myId = "hlpimg"+ %formname.name + "divZZ_RECRUITERS\\$0";
require(['jquery'], function ($) {
        $(document).ready(function() {
          $("#"+myId)
    .prependTo("#ZZ_RECRUITERS\\$0\\#1\\#");
          });
      });

Above I am using jQuery to move the popup help element to be between the label and the table data that allows you to resize the column header.

Here is the end results:

Finding Changed PeopleCode

When promoting a project to our Test or QA environment from development, often we are missing changes that we intended to put into our PeopleSoft Project definition.  Whenever I have a project that takes me multiple weeks to complete I like to run this SQL to evaluate what PeopleCode events have been changed that don't exist in my project.

Bind :1 = OPRID that made the code changes.
Bind :2 = Project Name that is being promoted.

SELECT * FROMPSPCMPROG A 
WHERE LASTUPDOPRID = ':1' AND LASTUPDDTTM > (sysdate - 19)
AND NOT EXISTS ( SELECT 'X' FROM PSPROJECTITEM X WHERE X.PROJECTNAME = ':2'
                  AND X.OBJECTID1 = A.OBJECTID1
                  AND X.OBJECTVALUE1 = A.OBJECTVALUE1
                  AND X.OBJECTID2 = A.OBJECTID2
                  AND X.OBJECTVALUE2 = A.OBJECTVALUE2
                  AND X.OBJECTID3 = A.OBJECTID3
                  AND X.OBJECTVALUE3 = A.OBJECTVALUE3
                  AND X.OBJECTID4 = A.OBJECTID4
                  AND X.OBJECTVALUE4 = A.OBJECTVALUE4); 

Conditional Required Fields (SetCursorPos + ^)

When I need to conditionally set a field as Required I paste this code into my SaveEdit of the field I need to direct the user's attention.

 ^.Style = "";
If ZZ_REQUISITIONS.PER_ORG.Value = "CWR" And
      None(^.Value) Then
   SetCursorPos(%Page, ^, CurrentRowNumber());
   ^.Style = "PSERROR";
   Error MsgGet(14907, 1006, "Required Field", ^.GetLongLabel());
End-If;

This works just as well:

 &this = GetField();
&this.Style = "";
If ZZ_REQUISITIONS.PER_ORG.Value = "CWR" And
      None(&this.Value) Then
   SetCursorPos(%Page, ^, CurrentRowNumber());
   &this.Style = "PSERROR";
   Error MsgGet(14907, 1006, "Required Field", &this.GetLongLabel());
End-If;

The "^" will be replaced with the current Record.FieldName and Message 14907, 1006 is
"%1 is a Required Field".  In this example the Job Requisition is for a Contingent Worker, your optional condition will depend on your requirements.

Handy CI Method for Job Data CIs

Here is a handy CI Method for the Job Data component that will set the Effective Sequence for a newly entered Job Data row.

CI Method code:

 Function setEffSeq
 
  Local Rowset &job;

   &job = GetRowset(Scroll.JOB);

  If &job(1).JOB.EFFDT.Value = &job(2).JOB.EFFDT.Value Then

      &job(1).JOB.EFFSEQ.Value = &job(2).JOB.EFFSEQ.Value + 1;

     End-If;

End-Function;

Screen Shot of Method in CI

This method is used after adding a new job row via the CI and then call this method to set the next effective sequence number.

Sample CI Code:

    &oSession = %Session;
    &oSession.PSMessagesMode = 1;
    &oZzCiJobData = &oSession.GetCompIntfc(CompIntfc.ZZ_CI_JOB_DATA);

 

 &oZzCiJobData.InteractiveMode = False;

 &oZzCiJobData.GetHistoryItems = True;

 &oZzCiJobData.EditHistoryItems = False;





 &oZzCiJobData.GET_EMPLID = [*];

 &oZzCiJobData.GET_EMPL_RCD = [*];



 rem ***** Execute Get *****;

 If Not &oZzCiJobData.Get() Then

  rem ***** No rows exist for the specified keys.*****;

  errorHandler();

  throw CreateException(0, 0, "Get failed");

 End-If;



    &oJobCollection  = &oZzCiJobData.JOB;



    &oJob = &oJobCollection.InsertItem(1); 



    &oJob.EFFDT = %DATE; 

    &oJob.action = "DTA"; 

    &oZzCiJobData.setEffSeq();

Even Better, use it in CI Template

A Better Way to Code with CIs

Security

Don't forget to Grant Security to your Method!

Service Operations in PUM images

Whenever we used to apply Maintenance Packs in the past they typically would not touch Service Operations, but I am discovering now that PUM images are built of future tools releases and we are seeing our Service Operations, Handlers and routings be Inactivated as part of the PUM image.  A good way to compare them to production is to use SQL to identify what Service Operation are being used in Production and then use the output of the production SQL as input into your development environment to make sure you have activated all the service operation's and their handlers and routings in development and QA if applicable.

Service Operations

Run this SQL in Production:

select IB_OPERATIONNAME||VERSIONNAME
from PSOPRVERDFN where active_flag = 'A';

Use the output of this query in the environment impacted by your  PUM ## image:

select * from PSOPRVERDFN where active_flag = 'I' AND IB_OPERATIONNAME||VERSIONNAME IN ('GETFACETNODESV1'

,'COMPETENCY_FULLSYNC3VERSION_1'

,'DOORACLEFACETSEARCHV1'...**output from sql above***)

The rows returned will show you the Service Operations that were turned off by your PUM Image.

Handlers

Run this SQL in Production

SELECT IB_OPERATIONNAME||HANDLERNAME 

FROM PSOPRHDLR WHERE ACTIVE_FLAG = 'A';

Use the output of this query in the environment impacted by your  PUM ## image:

SELECT IB_OPERATIONNAME||HANDLERNAME FROM PSOPRHDLR 

WHERE IB_OPERATIONNAME||HANDLERNAME IN ('PTFP_GETFEEDREQUESTHDLR'

,'PTFP_GETFEEDLISTREQUESTHDLR'

,'PTFP_GETPREPUBFEEDREQUESTHDLR''...**output from sql above***)

 AND ACTIVE_FLAG = 'I';

The rows returned will show you the Handlers that were turned off by your PUM Image.

Routings

Run this SQL in Production

SELECT ROUTINGDEFNNAME 

FROM PSIBRTNGDEFN WHERE AND EFF_STATUS = 'A';

Use the output of this query in the environment impacted by your  PUM ## image:

SELECT *  FROM PSIBRTNGDEFN

WHERE ROUTINGDEFNNAME IN (

'~GENERATED~23086336'

,'~GENERATED~23110751'

,'~GENERATED~23132114'...**output from sql above***)

 AND EFF_STATUS = 'I';

The rows returned will show you the Routings that were turned off by your PUM Image.

This post is not intended to replace compare reports, but this SQL can used to spot check your work.  

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.