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.