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.
PeopleSoft Development
Wednesday, October 12, 2016
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.
Thursday, September 15, 2016
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.
Wednesday, September 14, 2016
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.
Here is the end results:
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:
Thursday, September 1, 2016
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.
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);
Friday, August 26, 2016
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.
This works just as well:
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.
^.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.
Thursday, June 23, 2016
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.
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.
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! |
Thursday, June 16, 2016
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';
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.
Subscribe to:
Posts (Atom)