WorkCenter Pages

Why wait for 9.2 to utilize WorkCenter pages?  PeopleSoft describes WorkCenter pages as a paradigm shift on how to work gets done in PeopleSoft.  I have to agree, this is a major step for PeopleSoft in usability.

Here is the WorkCenter Page I built for Manager's to create, manage and approve Job Opening Requistions.

According to PeopleBooks these are the design consideration for WorkCenter Pages.

When you create a WorkCenter page, consider the following differences and page design recommendations, which accommodate those differences:

• For WorkCenter pages, the initial page in the target content area is determined by the menu item associated with the content reference that you select when you create a new WorkCenter page.
  While the page that appears initially in the target content area can be any page, you might want to start with an informational page to acquaint users with the WorkCenter page functionality and layout. You can use the sample WorkCenter page that comes with all PeopleSoft applications, or create a page that you design specifically for use as a WorkCenter page.
  
• WorkCenter pages should not generate a search page or start the search process when the user clicks the content reference to access the page. The user should immediately see the initial page.
  Select a “dummy” record—one that has no keys or contains only one row of data—as the component search record on the Use tab.
  The INSTALLATION record is an example of a “dummy” search record.
  
• WorkCenter pages generally require no save functionality.
  Select the Disable Saving Page check box on the Use tab.
  
• Typically, the component should consist of only one page, but no tabs or page links should appear on the page.
  Deselect the Multi-Page Navigation check boxes on the Internet tab.
  
• The elements that make up the tool bar and page bar are not applicable and should not appear on the WorkCenter page.
  Select the Disable Toolbar and Disable Pagebar check boxes on the Internet tab.
  
  
  
  
  What we were trying to accomplish is to give the Manager a one stop location to work job openings.  We created three Pagelets using the pagelet wizard that displayed all open requisitions within their organization ,all requisitions that were pending their approval and a navigation collection that allowed them to manage delegation.  We also developed an iScript based pagelet that presented all the positions they could create a requisition for in an easy to use jQuery tree.  Please note, all Pagelets need to be defined as Template Pagelets.
  
  

  

  Select Template Pagelet

  Then we built a Landing Page Component that presented instructions to the user.  A Requisitions component that was populated based on the Position Data record selected from the jQuery Tree.  And two components that allowed the user to select or proxy as another user.
  
  After all your pagelets and components are put together you are ready to navigate to the the Manage WorkCenter Pages.
  
  This Component can be found using the Main Menu via PeopleTools >> Portal >>WorkCenter >> Manage WorkCenter Pages.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  Use the "Create New WorkCenter Page" link to open a menu navigation to select your content reference to associate to your WorkCenter.  This will be your Landing Page, it should present your user with information regarding the WorkCenter page and should suppress the search page.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  This will bring you to the Assign Template Pagelets page.  Here you can define the workcenter layout and group you pagelets by tab via the Pagelet groups. 
  
  
  
  
  
  After you save your changes, the Content reference you selected in step one of the workcenter configuration will now consist of the target content and pagelet area.
  
  Brilliant! 

jQuery-Tree Project

This is a project on github that turns an HTML unordered list into a tree. And trees make navigating hierarchical data like department, structured via a department tree, or position data very intuitive.   I personally have enjoyed playing around with this project and believe it could be utilized to make PeopleSoft more user friendly when combined with workcenter pages.  So in this post I would like to show how you can create an HTML unordered list using position data and play around with this project.

First we need to create an HTML unordered lists.  That is where the previous post comes in, in that post I showed how you can use CTE to compare the current row to the next row or even the previous row.  If we change the sql just a bit to use a union rather than a left outer join it will simplify how we can use the sql to generate our html list.


This is the SQL I used in Oracle (please note the orgcode is only populated after the POS006A.sqr is run):

With Cte  As (Select Rownum Rownumber, Position_Nbr, Length(Orgcode)/ 3 As Level1    From Ps_Position_Data Where Orgcode Like (Select Orgcode||'%' From Ps_Position_Data X Where X.Position_Nbr = '?' And X.Orgcode > ' ')  Order By Orgcode)
 Select c1.rownumber,case c1.rownumber when 1 then '<ul class="org"><li><span>' else '<li><span>' end  As linebegin ,C1.Position_Nbr, CASE  WHEN C2.Level1 < C1.Level1 THEN '</span></li>'|| RPAD ('</ul>', LENGTH('</ul>')*(C1.Level1 - C2.Level1), '</ul>') ELSE Case C2.Level1 - C1.Level1 When 0 Then '</span></li>' Else  '</span><ul>'   End END As Htmlend  From Cte C1 , Cte C2 Where C1.Rownumber = C2.Rownumber - 1 
 Union
 Select Lastrow.Rownumber,'<li><span>' As linebegin ,Lastrow.Position_Nbr,  '</span></li>'||Rpad ('</ul>', Length('</ul>')*(Lastrow.Level1 - Firstrow.Level1), '</ul>') Endline From Cte Lastrow, Cte Firstrow Where Lastrow.Rownumber = (Select Count(*) From Cte) And Firstrow.Rownumber = 1 ;


 Use this SQL and replace the '?' mark with a position number that has multiple reports, for the purpose of this exercise, don't use the CEO, pick someone with a fair amount of direct reports, in my case I picked 02010087, because he had 103 direct and indirect reports.  Also use someone who has multiple levels below them.  Run the SQL and copy the results  (Minus the rownumber column)

<ul class="org"><li><span>    02010087    </span><ul>
<li><span>    02007923    </span><ul>
<li><span>    02008014    </span></li>
<li><span>    02008027    </span></li>
<li><span>    02008041    </span></li>
<li><span>    02008165    </span></li>
<li><span>    02009161    </span></li>
<li><span>    02010837    </span></li>
<li><span>    02011087    </span></li>
<li><span>    02019820    </span></li>
<li><span>    02020540    </span></li>
<li><span>    02020652    </span></li>
<li><span>    02020752    </span></li>
<li><span>    02020849    </span></li></ul>...:

Take your results and test them out using w3schools
 
If you copy your sql results (without the rownumber) between the <body> tag and press the submit code button your results should look like this:

 Now if we add the JavaScript we can even test the jQuery Tree right here!


Add this JavaScript after the <HTML> Tag and press the submit code button:

<script src="https://github.com/pioz/jquery-tree/raw/master/jquery.min.js" type="text/javascript">
</script>
<script src="https://github.com/pioz/jquery-tree/raw/master/jquery.cookie.js" type="text/javascript">
</script>
<script src="https://github.com/pioz/jquery-tree/raw/master/jquery.tree.js" type="text/javascript">
</script>
<script type="text/javascript">
   $(document).ready(function( ) {
     $('ul.org').tree({default_expanded_paths_string : 'all'});
   });
</script> 

 Now if you change your HTML to make the Position number to a link to some content regarding the position number and show the title and maybe even the incumbent, then this could be turned into a pagelet that would work really well within a workcenter. 


Thank you! Enrico Pilotto for a very cool project! 

Compare the Current Row to Next Row in SQL

Using a CTE one can compare the current row of data to the next row or even the previous row.  For example we can use the sql from the OrgCode Post.

 SELECT * FROM PS_POSITION_DATA WHERE ORGCODE LIKE (SELECT ORGCODE||'%' FROM PS_POSITION_DATA X WHERE X.POSITION_NBR = ? AND X.ORGCODE > ' ')  ORDER BY ORGCODE;


If we modify this sql just to select the current rownumber, position number, the length of ORGCODE divided by 3( I will tell you why in a second)  then we have the following SQL below:


With Cte  As (Select Rownum Rownumber, Position_Nbr, Length(Orgcode)/ 3 As Orglevel From Ps_Position_Data Where Orgcode Like (Select Orgcode||'%' From Ps_Position_Data X Where X.Position_Nbr = '02010087' And X.Orgcode > ' ')  Order By Orgcode)
 select * from CTE


This sql will return the following results:

Where ORGLEVEL is the degrees of separation from the Top Position, i.e. CEO.  And the Rownumber column will be used by us to join the current sql row to the next sql row.  This will allow us to determine if the next row reports the current row or if this row it is a peer.


  With Cte  As (Select Rownum Rownumber, Position_Nbr, Length(Orgcode)/ 3 As Level1    From Ps_Position_Data Where Orgcode Like (Select Orgcode||'%' From Ps_Position_Data X Where X.Position_Nbr = '02010087' And X.Orgcode > ' ')  Order By Orgcode)
 Select c1.rownumber, c2.rownumber, C1.Level1 , C2.Level1,  C1.Position_Nbr Cur_Position_Nbr, C2.Position_Nbr Prev_Position_Nbr,C1.Level1 - C2.Level1 separation  From Cte C1 Left Outer Join Cte C2 On C1.Rownumber = C2.Rownumber + 1 order by c1.rownumber;

This sql allows us to use the rownumber to join the current row number to the next in SQL.

SQL to Seperating Name Suffix from Last Name

Here is a cool piece of SQL that will separate the Name Suffix from the Last Name.  Since name suffix is a legal part of the name we collect it.  But our recruiting software vendor does not collect name suffix in a separate field and recommends that we pass it to PeopleSoft in one field, the last name field.  Since, by law the name on your w2 is suppose to match the name on your ssn card, we really want to collect this data and store it in its appropriate field.

This is where the SQL below comes in, this sql takes one input, Last Name, and returns two outputs.  Last Name and Name Suffix.    For Instance if you pass it the Last Name Weaver jr., it will return Weaver as the last name and Jr. as the Name Suffix.   If you pass it St. John, it would return St. John as the last name and blank as the Name Suffix.


Select Case Substr(Upper(:1) ,(Instr(:1 ,' ') + 1) ,(Length( :1)- Instr( :1 ,' '))) When 'JR' Then Substr(:1 , 1 ,(Instr( :1 ,' ') - 1)) When 'III' Then Substr(:1 , 1 ,(Instr( :1,' ') - 1)) When 'SR' Then Substr(:1 , 1 ,(Instr( :1 ,' ') - 1)) When 'II' Then Substr(:1 , 1 ,(Instr( :1 ,' ') - 1)) When 'IV' Then Substr(:1 , 1 ,(Instr( :1 ,' ') - 1)) Else :1 End ,  Case Substr( Upper(:1) ,(Instr( :1 ,' ') + 1) ,(Length( :1)- Instr( :1 ,' '))) When 'JR' Then 'Jr.' When 'III' Then 'III' When 'SR' Then 'Sr.' When 'II' Then 'II' When 'IV' Then 'IV' Else ' ' End
    FROM DUAL

The only problem with this SQL is it starts from the position 1 and finds the first white space, and with name suffix we need to find the last white space. So if you pass it St. John Jr. it would return St. John Jr. in the last name and blank in the name suffix.  So one small change will allow the INSTR function to start from the end of the string and search backwards for the last white space so this will work correctly.


 Select Case Substr(Upper(:1) ,(Instr(:1 ,' ', - 1) + 1) ,(Length( :1)- Instr( :1 ,' ', -1))) When 'JR' Then Substr(:1 , 1 ,(Instr( :1 ,' ', -1) - 1)) When 'III' Then Substr(:1 , 1 ,(Instr( :1,' ') - 1)) When 'SR' Then Substr(:1 , 1 ,(Instr( :1 ,' ', -1) - 1)) When 'II' Then Substr(:1 , 1 ,(Instr( :1 ,' ', -1) - 1)) When 'IV' Then Substr(:1 , 1 ,(Instr( :1 ,' ', -1) - 1)) Else :1 End ,  Case Substr( Upper(:1) ,(Instr( :1 ,' ', -1) + 1) ,(Length( :1)- Instr( :1 ,' ', -1))) When 'JR' Then 'Jr.' When 'III' Then 'III' When 'SR' Then 'Sr.' When 'II' Then 'II' When 'IV' Then 'IV' Else ' ' End
    FROM DUAL 

Building SOAP Message for 3rd Party Integration

If you need to integrate with a third party system using SOAP in PeopleSoft, you can save some time by utilizing HTML objects to store your SOAP Envelope in PeopleSoft.  For instance, BrassRing's Dispatch service has a web service flavor that uses the following SOAP Message.

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <Dispatch xmlns="http://trm.brassring.com/HRIS">
      <HRXML/>
    </Dispatch>
  </soap:Body>
</soap:Envelope>


You could use the the xmldoc class to create this exact schema and then send it in your message to BrassRing, or you can do something like this.

 &DispatchWebService = CreateXmlDoc(GetHTMLText(HTML.ZZ_DISPATCH_SOAP));
  
  &node = &DispatchWebService.DocumentElement.FindNode("/*[local-name()='Envelope']/*[local-name()='Body']/*[local-name()='Dispatch']/*[local-name()='HRXML']");
   
Rem add message detail to hrxml node for brassring to process;
&nodeValue = &node.AddText(&Message.GenXmlString());


Where HTML.ZZ_DISPATCH_SOAP is equal to the SOAP schema above.  The only issue with this method is that it will not work in an Application Engine, as you cannot use the GetHTMLText function in AE.  So you can simply write your own Function to pull the SOAP Envelope out, that way you can keep your code agnostic and only have one version of it running in AE and online.


Function get_html_text(&html As string) Returns string
   rem change this to record object;
   SQLExec("Select Contdata From  Pscontent Where Conttype = 4 And Contname = :1", &html, &blob);   
   Return &blob; 
  
End-Function;


Now you change your CreateXMLDoc Call to this:


 &DispatchWebService = CreateXmlDoc(Get_HTML_Text("ZZ_DISPATCH_SOAP"));

 Now you are ready to send this message in SOAP.