Friday, December 13, 2013

Creating Backup Projects for Change Management

First of all, we don't utilize a change management tool like Stat or Phire.  But that does not mean we don't create backups projects for our changes.  Typically I log into Production and manually create a project and export it to file, but recently I had a project that was going to change 50+ objects.  So I asked my Admin if he had any ideas on moving just the project definition, without the objects, so I could create a backup project from Production.   He told me that if I export the project to a file in Development and then used the "compare and report from file" in Production it would copy my project definition.

This worked and I was able to quickly export my backup file from Production!  


Thursday, December 5, 2013

Dynamic CTE Prompt table

I was asked to create a prompt record for the Recruiter prompt on our Job Requisitions to make it easier for our Talent Acquisition team. I analyzed the data and could not filter it down to a couple of Jobcodes, then I realized 90% of the associates that reported to the head of Talent Acquisition were recruiters. So I got to thinking, that a CTE (Common Table Expresion) would work really well in this scenario.  So I created a field on our custom installation record that identified the head of Talent Acquisition.  Then I wrote this SQL.

WITH REPORTS ( OPRID, NAME, ROLEUSER_SUPR) AS (
 SELECT A.roleuser
 , A.descr
 , A.ROLEUSER_SUPR
  FROM PS_ROLEXLATOPR A
  , PS_ZZ_INSTALLATION I
 WHERE A.OPRID = I.ROLEUSER_SUPR
  UNION ALL
 SELECT A.OPRID
 , A.DESCR
 , A.ROLEUSER_SUPR
  FROM PS_ROLEXLATOPR A
  , REPORTS X
 WHERE  A.ROLEUSER_SUPR = X.OPRID
   AND A.OPRID > ' ')
 SELECT *
  FROM REPORTS

Here is my record Definition:


Now this assumes that you are keeping the roleuser_supr updated.  But if you are keeping it updated then you can write a recursive CTE to return all direct and indirect reports to a position.




Formatting Currency

We built a custom Job Opening Workcenter in PeopleSoft to integrate job requisitions to BrassRing and one of the challenges was formatting the Mid, Min and Max for the Salary Ranges.  We sent over the numeric value, but BrassRing said that if we wanted it formatted, ie. $999,999.99, that we need to send it formatted.  At first I tried using the Field's "formattedValue" property , but this only populated when called in the component buffer.  So when I sent future effective dated changes in Batch, I would need to format these values without the help of the formattedValue.  I searched in PeopleCode and could not find a delivered function to handle my formatting.  So I wrote my own method that I call when publishing updates. 


method format_currency
   /+ &value as String +/
   /+ Returns String +/
   Local string &formattedValue, &partValue;
   Local array of string &inputValue;
   Local number &x, &inputLength, &start, &end;
   &inputValue = Split(&value, ".");
   &inputLength = Len(&inputValue [1]);
  
   rem place the comma;
  
   For &x = &inputLength To 1 Step - 3
      &start = &x - 2;
      If &start > 0 Then
         &partValue = "," | Substring(&inputValue [1], &start, 3);
      Else
         &end = Mod(&inputLength, 3);
         &partValue = Substring(&inputValue [1], 1, &end);
      End-If;
      &formattedValue = &partValue | &formattedValue;
   End-For;
   rem remove any extra commas;
   If Substring(&formattedValue, 1, 1) = "," Then
      &formattedValue = Substring(&formattedValue, 2, (Len(&formattedValue) - 1));
   End-If;
  
   rem add dollar sign and cents;
   If &inputValue.Len = 1 Then
      &formattedValue = "$" | &formattedValue | ".00";
   Else
      &inputValue [2] = &inputValue [2] | "00";
      &formattedValue = "$" | &formattedValue | "." | Substring(&inputValue [2], 1, 2);
   End-If;
  
  
   Return &formattedValue;
end-method











If someone else has had this challenge and found an easier way to accomplish this, please let me know.  Because, I spent way too much time coming up with this solution.


Wednesday, December 4, 2013

Custom Component Interface Methods

Component Interfaces have four standard methods, Cancel, Find, Get and Save.  But PeopleSoft also gives you the ability to create custom methods to handle a scenarios where you might want to do more than just Save the component.  For Instance, we have a Custom Component Interface that we use to process inbound job transfers and rehires from our third party recruiting system BrassRing.  In the case of rehires we need to purge our bolt-on UMB Data that we store on a custom page that we added to the Job Data Component.  This is a perfect use for a Component Interface Method.


First Open up your Component Interface in PeopleTools and double click on the METHODS in the Component Interface Properties pane.


This will open a window where you can define your Custom Method.


Now you will need to add Security to you Custom Method.






Now you can access your Method in your CI Code

&JobDataCI.RESET_UMB_DATA();

Or if you are using my Excel to CI, you can use the Method Option.




Other Uses.

  1. Updating Sequence number like the Effective Date Sequence on Job or the Sequence number for the Paysheet upload.
  2. Creating a Custom Get Method for Components that like Job Code Table or Department Table that use description as a list item, to select one item from the collection.   (Since they all take you to the same place.)
  3.  Conditionally updating data based in the component buffer based.  This last one might seem curious, but if you utilize a utility like my Excel to CI tool, then it makes perfect sense.