ACA Hours

I have been writing a view to pull hours for ACA Reporting.   To preface this we use TL and Payroll for North America, but since hours can be added to the paysheet, I thought it best not use the TL tables and get the hours directly from the Payroll.

First the Record Definition:

My view contains COMPANY, PAYGROUP, EMPLID, PAY_END_DT, ERNCD and TOT_HRS.  Below is my SQL for building this view:

WITH HOURS_WRK (COMPANY, PAYGROUP, EMPLID, PAY_END_DT, ERNCD , HOURS) AS (

 SELECT C.COMPANY

 , C.PAYGROUP

 , P.EMPLID

 , P.PAY_END_DT

 ,CASE P.REG_HRS WHEN 0 THEN P.ERNCD_REG_EARNS ELSE P.ERNCD_REG_HRS END

 , (P.REG_HRS + P.REG_EARN_HRS)

  FROM PS_PAY_EARNINGS P

  , PS_PAY_CHECK C

 WHERE P.COMPANY=C.COMPANY

   AND P.PAYGROUP=C.PAYGROUP

   AND P.PAY_END_DT=C.PAY_END_DT

   AND P.OFF_CYCLE=C.OFF_CYCLE

   AND P.PAGE_NUM=C.PAGE_NUM

   AND P.LINE_NUM=C.LINE_NUM

   AND P.SEPCHK=C.SEPCHK

   AND C.PAYCHECK_STATUS = 'F'

   AND P.OK_TO_PAY = 'Y'

  UNION ALL

 SELECT C.COMPANY

 , C.PAYGROUP

 ,c.emplid

 , c.pay_end_dt

 , ERNCD

 , a.oth_hrs

  FROM PS_PAY_OTH_EARNS A

  ,PS_PAY_EARNINGS B

  ,PS_PAY_CHECK C

 WHERE A.COMPANY=B.COMPANY

   AND A.PAYGROUP=B.PAYGROUP

   AND A.PAY_END_DT=B.PAY_END_DT

   AND A.OFF_CYCLE=B.OFF_CYCLE

   AND A.PAGE_NUM=B.PAGE_NUM

   AND A.LINE_NUM=B.LINE_NUM

   AND A.SEPCHK=B.SEPCHK

   AND A.ADDL_NBR=B.ADDL_NBR

   AND C.PAYCHECK_STATUS = 'F'

   AND B.OK_TO_PAY='Y'

   AND A.COMPANY=C.COMPANY

   AND A.PAYGROUP=C.PAYGROUP

   AND A.PAY_END_DT=C.PAY_END_DT

   AND A.OFF_CYCLE=C.OFF_CYCLE

   AND A.PAGE_NUM=C.PAGE_NUM

   AND A.LINE_NUM=C.LINE_NUM

   AND A.SEPCHK=C.SEPCHK)

 SELECT COMPANY

 , PAYGROUP

 , EMPLID

 , PAY_END_DT

 , ERNCD

 , HOURS

  FROM HOURS_WRK

After this view is built I can select my hours from the paycheck and sum them by emplid, paygroup, company and filter by earning code.  This is the sql we are using to pull last years hours:

SELECT EMPLID, SUM(TOT_HRS) FROM PS_ZZ_EE_PAY_C_HRS

WHERE ERNCD not in ('020', '030', '023') 

AND PAY_END_DT BETWEEN to_date('10/14/2013', 'mm/dd/yyyy')  

AND  to_date('10/12/2014', 'mm/dd/yyyy') 

group by emplid,paygroup;

If anyone thinks I am missing something or would add to this post leave a comment and we can start a discussion.

Folder Tab Framework

The HCSC_FOLDER_TABS is a Application Package for rendering folder tabs on a page.  While this does not sound overly exciting it is quite practical and easy to use.  Essentially this Application Package has the potential to change the way you design PeopleSoft pages, the unfortunate thing is this package is part of the HCM Shared Components and is not currently in FIN.


To utilize the HCSC_FOLDER_TABS you need to add an HTML area to your page in the location you want your tabs to be displayed.  Then you need to add HCSC_TAB_CLICK_SBP subpage to your page for the listener to be invoked via FieldChange PeopleCode.  Then you need to follow these four coding steps.  The first three steps need to be in a Postbuild or Postbuild equivalent event and then add fieldchange peoplecode to the component record for the DERIVED_TAB.TAB_BUTTON_ID.

1. Define the tab sets(s)
2. Instantiate Listener and controller
3. Link tabset, HTML area listener to controller
4. Add field change PeopleCode to execute your listener logic.

To define the Tabset you will have to create two arrays of string.  One array will contain the Tab names and the other the Tab labels

&asTabNames = CreateArrayRept("", 0);
&asTabLabels = CreateArrayRept("", 0);

&sTabSet = "ZZ_TEST";

/* 1 */

&asTabNames.Push("A");

&asTabLabels.Push("IDM");

&asTabNames.Push("B");

&asTabLabels.Push("Tab2");

The second step is to Instantiate Listener and controller.  There will be more on the Listener below as you will need to write your special logic to control the data being shown under each tab in your Listener.

/* 2 */

Local ZZ_ECP_TABS:TabListener &oTabListener;

&oTabListener = create ZZ_ECP_TABS:TabListener();

&coTabs = create HCSC_FOLDER_TABS:FolderTabs();

And final step of the PostBuild PeopleCode is to link tabset, HTML area listener to controller.

/* 3 */
&coTabs.DynamicSetupData(Page.ZZ_HCSC_TABS, &sTabSet, &asTabNames, &asTabLabels, Record.DERIVED_TAB.GetField(Field.HTML_AREA_01), Record.DERIVED_TAB.GetField(Field.HIDDEN_AREA), False, 0, &oTabListener);

&coTabs.ActivatePage(Page.ZZ_HCSC_TABS);

Then you need to add your Component FieldChange code on the TAB_BUTTON_ID.

import HCSC_FOLDER_TABS:FolderTabs;



Component HCSC_FOLDER_TABS:FolderTabs &coTabs;



REM Invoke Listener logic as user has pushed a tab;

&coTabs.HandleTabEvent();

The only thing left to do is write your listener logic in your HandleTabEvent method:


Below is my TabListener that I put together for this Test:

import HCSC_FOLDER_TABS:TabSetListener;


class TabListener extends HCSC_FOLDER_TABS:TabSetListener
   method TabListener();
   method SetStateForFolderTabs(&pPage As string, &pTabSet As string, &pTab As string);
private
   instance Rowset &rsA, &rsB, &rsC, &rsE, &rsF, &rsG;
end-class;

Component Rowset &idm;

REM ************************************************;
REM Constructor establishes pointers to page objects;
REM ************************************************;
method TabListener
   
   If %Component = Component.ZZ_HCSC_TABS Then
      &rsA = &idm;
   End-If;
end-method; /* TabListener */


REM ************************************************;
REM Control page content associated with selected tab;
REM ************************************************;
method SetStateForFolderTabs
   /+ &pPage as String, +/
   /+ &pTabSet as String, +/
   /+ &pTab as String +/
   
   
   &rsA.HideAllRows();
   
   Evaluate &pTab
   When "A"
      REM IDM;
      &rsA.ShowAllRows();
      Break;
   When "B"
      REM TAB 2;
      WinMessage(&pTab);
      Break;
   
      
   End-Evaluate;
   
   
end-method; /* SetStateForFolderTabs */

Below is a picture of what this demo code above produced.

Bookmarklet for Oracle Support

This is a bookmarklet I use for Oracle Support, it has been working for a couple of weeks. Click the button below to copy the bookmarklet and search it for the sting "your.email@domain.com" and replace this with your Oracle sign on email address and replace the 'yourpassword' text with your Oracle support password. If want to make it more secure you can add a input similar to the switch user bookmarklet in the previous post.

Bookmarklet for Switching Users

Here is a neat bookmarklet for switching users for testing purposes.  It requires the user id and password to be the same, this is pretty common testing scenario.

Had to make some modifications to the orginal posting to allow it to work with IE, but leaving my original bookmarklet below that works well with Firefox and Chrome.  Use the Click to Copy button to copy the bookmarklet to your clipboard.

javascript:var pswd = prompt("Enter TestUser", "");if (pswd!= null) {var1=window.top.location.pathname.match(/^\/ps[pc]\/(.+?\/)(.+?\/)(.+?\/)/);l=window.top.location.origin;l=l+"/psp/"+var1[1]+"?cmd=login";d=document;f=d.createElement("form");h=d.createElement("input");h1=d.createElement("input");f.setAttribute("method","post"); f.setAttribute("action", l);h1.setAttribute("type","hidden");h1.setAttribute("name","pwd");h1.setAttribute("value",pswd );h.setAttribute("type","hidden");h.setAttribute("name","userid");h.setAttribute("value",pswd);f.appendChild(h);f.appendChild(h1);d.body.appendChild(f);f.submit();}

Rebuilding Component Interface's Structure

We have added custom pages to components like JOB_DATA and PERSONAL_DATA to track organization specific data associated to those transactions.  I consider these types of customization as high value as they tend to meet the business needs without much maintenance.  But one of the maintenance cost is rebuilding the component interfaces that are built off of these two components, often when you add a custom page to the component you will need to also modify the delivered component interface's structure.  Simply search for objects that related to your modified component and start opening the component interfaces, if you get this error message then you should rebuild the CI's structure.

Simply open the CI and search for the Red 'X' over the Collection that needs to be rebuilt.

In the image above you can see that the COLL_JOB_USF needs to be fixed, to correct this delete the Collection and re-add it back to the CI.  Then you will need to re-label all the Properties to "PROP_" or "KEYPROP" and the Collection to "COLL_".  The Collection is of just one item so just key that manually, however the Properties can be a whole bunch of items.  In this example I had 125 to key, use the sql below to update all the properties to 'PROP_'.

UPDATE PSBCITEM SET BCITEMNAME = 'PROP_'||BCITEMNAME  WHERE   bcname = ':1' AND BCITEMPARENT = ':2';

:1 = CI_JOB_DATA

:2 = COLL_JOB_USF

Then add the 'KEY' to the KEY fields in the Collection.

Quick Macro for Scanning PeopleCode Compare Reports

Today I am preparing for our Upgrade and I was doing some analysis on the Record PeopleCode Compare reports that I had saved as Tab Delimited text files and imported into excel.  What I was doing was analyzing the deltas and documenting the event I found them.  This analysis would occasionally require me to scroll 100's of excel rows without finding a change in event or code!  This was starting to frustrate me.

Compare Report for Record PeopleCode

What I needed was a Macro that could scan the report for me and stop on either the column 1 having a value or column 8 having a value; both are highlighted in red above.  This was my simple Macro that I executed when I wanted to quickly scan to the next change or to the next event.

Sub scan()
Dim i As Long
Dim s As Long
Dim o As Long
 s = ActiveCell.Row
 For i = ActiveCell.Row To Rows.Count
  
 If Cells(i, 8).Value > " " Or Cells(i, 1) > " " Then
  o = i - s + 1
  ActiveCell.Offset(o, 0).Select
  
  Exit For
 End If

Next i
End Sub

Technology Radar

Being in IT for the past 15 years I have learned that technology changes quickly and what is hot today, often is not relevant tomorrow.  This makes learning new technology challenging as you don't want to invest your time into something you will have no use for in the future!  Since then I have learned about the Technology Radar, this website can help IT Staff, Managers and even CTO's keep current on the latest trends and stay on top of the ever changing landscape that is IT.

8.53 tools Site Name Bug

We recently upgraded our tools from 8.51 to 8.53 in preparation for our 9.2 Upgrade and by far this tools upgrade has caused us more issues then any tools upgrade I can remember.  The most dubious issue we have discovered is related to our Interaction Hub's site name.  We use the string 'PSP' in our site name and this was preventing the delivered Activity guides from working and we opened a case with vendor regarding this issue and they recommended that we remove the "PSP" from our site name as they knew about the bug, but they had not begun analyzing the fix.

We considered modifying or site name, but the effort would have caused a delay in our upgrade project and we were not willing to move our date to remove the "PSP" from our site name.  So I was asked to analyze the delivered JavaScript and determine the root cause of the "PSP" bug.

I traced the issue to the following Regular Expression that attempts to derive the URI from the current location.  Below is the Regular Expression:

      

       /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\

I have bolded the “?” since this appears to be the issue with the expression above.  A question mark tells the regular expression to match on one or zero times on the last “/”.  This means that the expression will match anything that begins with ‘/psc’ or ‘/psp’.  So if you remove the ‘?’ from the expression it will match only ‘/psc/’ or ‘/psp/’.    I believe this is the fix to the Sitename issue.

Below are all the locations I have modified in Portal, HCM and FIN to fix this Bug.

 

Portal

Searching for match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^...

EPPSC_ADDSC_JS.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("/psp/", "/psc/");

PORTAL_REFRESHPAGE.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PORTAL_REFRESHPAGE.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace('psp','psc');

PORTAL_REFRESHPAGE.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PORTAL_REFRESHPAGE.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0];

PTAI_NAVSUBPAGEHTML.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psp", "psp");

PTAI_PAGELET_JS.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAI_PAGELET_JS.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAI_PAGELET_JS.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAI_PAGELET_JS.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAI_PAGELET_JS.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAL_JS_PAGE.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psp", "psc");

PTPANEL_PAGELET_JS.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psp", "psc");

PT_COMMON.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0];

PT_COMMON.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PT_COMMON.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace('psp','psc');

PT_HP2_JS.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PT_HP2_JS.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0];

PT_IFRAME_HDR_JS.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PT_IFRAME_HDR_JS.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace('psp','psc');

PT_PORTAL_AS_JS.4 -- match(/\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace('psp', 'psc'),

21 occurrence(s) have been found.

HCM

Searching for /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\...

PORTAL_REFRESHPAGE.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PORTAL_REFRESHPAGE.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace('psp','psc');

PORTAL_REFRESHPAGE.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PORTAL_REFRESHPAGE.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0];

PTAI_NAVSUBPAGEHTML.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psp", "psp");

PTAI_PAGELET_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAI_PAGELET_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAI_PAGELET_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAI_PAGELET_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAI_PAGELET_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAL_JS_PAGE.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psp", "psc");

PTPANEL_PAGELET_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psp", "psc");

PT_COMMON.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0];

PT_COMMON.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PT_COMMON.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace('psp','psc');

PT_HP2_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PT_HP2_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0];

PT_IFRAME_HDR_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PT_IFRAME_HDR_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace('psp','psc');

PT_PORTAL_AS_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace('psp', 'psc'),

FIN

Searching for /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\...

PORTAL_REFRESHPAGE.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PORTAL_REFRESHPAGE.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace('psp','psc');

PORTAL_REFRESHPAGE.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PORTAL_REFRESHPAGE.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0];

PTAI_NAVSUBPAGEHTML.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psp", "psp");

PTAI_PAGELET_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAI_PAGELET_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAI_PAGELET_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAI_PAGELET_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAI_PAGELET_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psc", "psp");

PTAL_JS_PAGE.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psp", "psc");

PTPANEL_PAGELET_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace("psp", "psc");

PT_COMMON.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0];

PT_COMMON.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PT_COMMON.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace('psp','psc');

PT_HP2_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PT_HP2_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0];

PT_IFRAME_HDR_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//);

PT_IFRAME_HDR_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace('psp','psc');

PT_PORTAL_AS_JS.4 -- /\/ps(c|p)\/?([^\/]*)?\/?([^\/]*)?\/?([^\/]*)?\//)[0].replace('psp', 'psc'),

20 occurrence(s) have been found.

Unified Navigation Setup

If you are looking for a good resource for setting up Unified Navigation check out this Blog Post at Remote PSAdmin.  Now if you get through this blog and everything is working through the folder setup, but not in your Navigation Collections? Then you need to look a these additional setup steps.

One, make sure all users have access to the PTCS_GETAUTHORIZATION service operation in all environments that are going to utilize Unified Navigation.  This Service Operation is used in the PORTAL_CREF_ADM.PORTAL_OBJNAME FieldFormula peoplecode when building the user's homepage to determine if they have access to links within Navigation Collections or Pagelets from remote nodes.  

Second, make sure that your portal URI for your content provider matches the Integration Broker node for that content provider. So when you define a remote node for a navigation collection and you use the HRMS node in your Portal environment there is a function, isIBNetworkNode, in the PORTAL_CREF_ADM.PORTAL_OBJNAME FieldFormula peoplecode that is using the Portal URI to match the Portal Node to the Integration Broker node.  If this function cannot match your HRMS nodes Portal URI to an Integration Broker portal URI then it will not fire the message from step one and your Navigation Collection link will not be presented to your users.

  

HCM nodes defined in my Portal Environment.

Excel to CI Webinar

In case you missed my first ever Webinar you can find it here.

http://www.questdirect.org/learn/2666442/

And here is the documentation you will want to have handy while watching the webinar.

Using Related Content and jQuery lightbox to turn any PeopleSoft Transaction into a Dashboard.

In my two previous posts I showed how to implement a Colorbox and use Pagelet Wizard in related content to load up to 25,000 rows of related data into an HTML table.  In the Pagelet Wizard posting it will load the first 100 rows and fetch the next 100 rows as the user scrolls the table.  But as I noted in that posting if you define a link in Pagelet Wizard it will overlay the Related Content Area frame and your related data table will be gone.  Since I did not think this was a great UX for the user, I really wanted a way to open this content into a jQuery lightbox, I choose Colorbox based on its flexiblity and its friendly license.

When I examined the HTML that was being generated by Peoplesoft I noticed it was loading the content into three seperate iFrames.

There is a TargetContent Frame, this is the largest area on the page and would be the best location to present the content from the related content.  Then there are two related content frames, the Related Content frame and a child frame called the related content area frame.  The question was how do I load the content from the child frame to the uncle frame?  I call the TargetContent frame an uncle, because it is on the same level as the Related Content frame.  In order to accomplish this I needed a lightbox that could be called from the jQuery Namespace from the child frame.  In my research I found two lightboxes that were suitable for this type of use.  First was the Fancybox2, it is a very good jQuery lightbox, but it also requires a license be purchased for Commercial development.  Now that is only $89 for multi-domains or $19 for one domain, but still it has a cost beyond learning how to use it.  The second option is the Colorbox, this is the option I went with since it is lightweight, flexible and licensed under the MIT License.  

Now if you have already followed the steps of my previous 2 postings, Pagelet Wizard for Related Content and Colorbox. Then all you need to do is modify the iScript in the Pagelet Wizard post to deliver the Colorbox to the transaction.

This is my modified iScript:

import PTPPB_PAGELET:*;
import PTPPB_PAGELET:DataSource:*;
import ZZ_JSON:JSONEncoder;

Local XmlDoc &xmlDoc;

Function IScript_RLC_CSS()
   %Response.SetContentType("text/css");
   %Response.Write(GetHTMLText(HTML.ZZ_RLC_SWAN_CSS));
End-Function;

Function json()
   Local array of XmlNode &rows = &xmlDoc.DocumentElement.FindNodes("/root/table/tbody/tr/td/table/tbody/tr");
   Local array of XmlNode &td;
   Local ZZ_JSON:JSONEncoder &utility = create ZZ_JSON:JSONEncoder();
   Local boolean &isFirstRow = True;
   
   %Response.Write("[");
   rem start on row two to avoid the table headers;
   For &x = 2 To &rows.Len
      If (&isFirstRow) Then
         &isFirstRow = False;
      Else
         %Response.Write(", ");
      End-If;
      %Response.Write("{""trClass"": """ | &utility.encode(&rows [&x].GetAttributeValue("class")) | """, ""valign"": """ | &utility.encode(&rows [&x].GetAttributeValue("valign")) | """,  ""td"": [");
      &td = &rows [&x].FindNodes("td");
      &isFirstData = True;
      For &y = 1 To &td.Len
         If (&isFirstData) Then
            &isFirstData = False;
         Else
            %Response.Write(", ");
         End-If;
         &Anchor = &td [&y].FindNode("A");
         If &Anchor.isnull Then
            %Response.Write("""" | &utility.encode(&td [&y].NodeValue) | """");
         Else
            %Response.Write("{""href"": """ | &utility.encode(&Anchor.GetAttributeValue("href")) | """, ""title"": """ | &utility.encode(&Anchor.GetAttributeValue("title")) | """,  ""value"": """ | &Anchor.nodevalue | """}");
         End-If;
      End-For;
      %Response.Write("]}");
   End-For;
   %Response.Write("]");
End-Function;




Function iScript_Request()
   Local array of string &bindArray = CreateArray(%Request.GetParameter("BIND1"), %Request.GetParameter("BIND2"), %Request.GetParameter("BIND3"), %Request.GetParameter("BIND4"), %Request.GetParameter("BIND5"), %Request.GetParameter("BIND6"), %Request.GetParameter("BIND7"), %Request.GetParameter("BIND8"), %Request.GetParameter("BIND9"), %Request.GetParameter("BIND10"));
   Local array &dsParms = CreateArray();
   
   Local string &START = %Request.GetParameter("START");
   Local string &AJAX = %Request.GetParameter("AJAX");
   Local string &PAGELETID = %Request.GetParameter("PAGELETID");
   Local number &chunk = 100;
   
   Local PTPPB_PAGELET:PageletWizard &PWAPI = create PTPPB_PAGELET:PageletWizard();
   
   Local PTPPB_PAGELET:Pagelet &thisPagelet = &PWAPI.getPageletByID(&PAGELETID, True);
   
   
   SQLExec("select PTPPB_VALUE from ps_PTPPB_PGLT_PRMS where PTPPB_PAGELET_ID = :1 and PTPPB_FIELDNAME = 'QueryName'", &PAGELETID, &QryName);
   
   &ajaxQS = "?AJAX=Y&PAGELETID=" | &PAGELETID;
   For &x = 1 To &bindArray.Len
      If All(&bindArray [&x]) Then
         Local string &thisBind = "BIND" | String(&x);
         
         SQLExec("SELECT FIELDNAME||'.'||BNDNUM FROM PSQRYBIND WHERE OPRID = ' ' AND QRYNAME = :1 AND BNDNAME=:2", &QryName, &thisBind, &qryBind);
         REM MAP BIND VARIABLES TO THE RLC QUERY DATASOURCE PARAMETERS;
         Local PTPPB_PAGELET:DataSource:DataSourceParameter &Temp = &thisPagelet.DataSource.addParameter(&qryBind, &bindArray [&x]);;
         &dsParms.Push(&Temp);
         &ajaxQS = &ajaxQS | "&" | &thisBind | "=" | &bindArray [&x];
      End-If;
   End-For;
   
   
   &loadURL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_RLC, Field.ISCRIPT1, "FieldFormula", "iScript_Request") | &ajaxQS | "&START=";
   &cssURL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_RLC, Field.ISCRIPT1, "FieldFormula", "IScript_RLC_CSS");
   &jQueryURL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_APT_JSL, Field.ISCRIPT1, "FieldFormula", "IScript_jQuery_1_7_1");
 rem  &FANCYBOX_CSS_URL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_FBOX2, Field.ISCRIPT1, "FieldFormula", "IScript_FancyboxCss2");
 rem  &FANCYBOX_PACK_URL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_FBOX2, Field.ISCRIPT1, "FieldFormula", "IScript_FANCYBOX_2_1_5_PACK");
   &COLORBOX_CSS_URL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_CB, Field.ISCRIPT1, "FieldFormula", "IScript_colorbox_css");
   &COLORBOX_JS_URL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_CB, Field.ISCRIPT1, "FieldFormula", "IScript_ColorBoxJs");
   
   
   If &AJAX <> "Y" Then
      &AJAX = "N";
      Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsStart = &thisPagelet.DataSource.addParameter("START", "1");
   Else
      Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsStartAJAX = &thisPagelet.DataSource.addParameter("START", &START);
      %Response.SetContentType("application/json");
   End-If;
   
   Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsParmq = &thisPagelet.DataSource.addParameter("QueryName", &QryName);
   Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsParmR = &thisPagelet.DataSource.addParameter(".MAXROWS", String(&chunk));
   Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsParmR1 = &thisPagelet.DataSource.addParameter("AJAX", &AJAX);
   
   &string = &thisPagelet.Execute();
   
   
   REM &html = GetHTMLText(HTML.ZZ_RLC_REQUEST, &loadURL, &string, &chunk, &cssURL, &jQueryURL, &FANCYBOX_CSS_URL, &FANCYBOX_PACK_URL);
   &html = GetHTMLText(HTML.ZZ_RLC_REQUEST, &loadURL, &string, &chunk, &cssURL, &jQueryURL, &COLORBOX_CSS_URL, &COLORBOX_JS_URL);
   
   If &AJAX = "N" Then
      
      %Response.Write(&html);
      
   Else
      REM DO JSON PROCESSING;
      &xmlDoc = CreateXmlDoc("" | &string | "");
      
      json();
      
   End-If;
   
End-Function;

Then modify the HTML.ZZ_RLC_REQUEST JavaScript to load jQuery in the TargetContent Frame and then load the Colorbox.js if the user request the content.

<script type="text/javascript"  src="%BIND(:5)"></script>
<script type="text/javascript"  src="%BIND(:7)"></script>

<script type="text/javascript">

function setcolorbox() {
    $('a').click(function() {
       if (typeof tgtFrame.contentWindow.$.colorbox ==='undefined') {
        var thisHref = this.href;
        tgtFrame.contentWindow.$.getScript(colorbox,function() {
        var pscUrl = thisHref.replace("/psp/", "/psc/");
        tgtFrame.contentWindow.$.colorbox({href : pscUrl,  iframe: true,  height: '90%', width: '95%'})});
        return false;
       }
       else
       {
       var pscUrl = this.href.replace("/psp/", "/psc/");
       tgtFrame.contentWindow.$.colorbox({href : pscUrl,   iframe: true,  height: '90%', width: '95%'});
       return false; 
       } 
    });
};

function importStylesheet(sheetUrl) {
  var ss = document.createElement("link");
  ss.rel = "stylesheet";
  ss.href = sheetUrl;
  ss.type = "text/css";
  document.getElementsByTagName("head")[0].appendChild(ss);
}
  importStylesheet("%bind(:4)");
  importStylesheet("%bind(:6)");

 
var processing;
var chunk = %bind(:3);
var StartRow = chunk + 1;
var iScriptURL = "%bind(:1)";
var colorboxCss = "%bind(:6)";
var colorbox = "%bind(:7)";



var tgtFrame = window.top.document.getElementById('ptifrmtgtframe');
var tgtHead = tgtFrame.contentWindow.document.getElementsByTagName('Head');

var fbCss = document.createElement("link");
fbCss.rel = "stylesheet";
fbCss.href = colorboxCss;
fbCss.type = "text/css";
 
var scriptJQ = document.createElement('script');
scriptJQ.type='text/javascript';
scriptJQ.src = "%bind(:5)";


tgtHead[0].appendChild(scriptJQ);
tgtHead[0].appendChild(fbCss);

$(document).ready(function(){
 
    setcolorbox();    
      
    $(window).scroll(function(e){
         
        if (processing)
            return false;

        if ($(window).scrollTop() >= ($(document).height() - $(window).height())*0.7){
          processing = true;
           iScriptURL2 = iScriptURL+StartRow;
            
            $.get(iScriptURL2, function(loaded){
var htmlRows = '';
StartRow = StartRow + chunk;
for (var i=0;i<loaded.length;i++)
{ 
  //debugger;
  htmlRows = htmlRows + "<tr class='"  + loaded[i].trClass + "' valign='" + loaded[i].valign + "'>";
   for (var d=0;d<loaded[i].td.length;d++)
   {
     if ( typeof loaded[i].td[d].href != 'undefined')
        {       
          htmlRows = htmlRows + "<td><a href='" + loaded[i].td[d].href +  "'>" + loaded[i].td[d].value +"</a></td>";          
        }
     else
        {
          htmlRows = htmlRows + "<td>" + loaded[i].td[d] + "</td>";
        }
   }
}
$('.PSLEVEL1GRID tr:last').after(htmlRows);
 //rem setup new rows to open in colorbox;
 setcolorbox();

if (loaded.length>0){
processing = false;
};
});
        };
    });
});
</script>

 
%bind(:2)

The results are just stunning!

Colorbox in PeopleSoft

My new favorite lightbox for PeopleSoft is Jack Moore's Colorbox.  It is released under the MIT License and is free to use and customize to your liking.  I use iScripts to deliver the Colorbox to my applications; all you need to do to implement the Colorbox in your instance is follow these instructions below.

Download the Colorbox and pick the example you like best from one of the 5 examples in the download.

After you have made your selection you need to open the images folder under the example# that you chose and start loading the example's images into PeopleSoft.  I chose example1, so I loaded 5 photos into Application Designer.

Then you need to create and HTML object and copy the CSS from the example you chose and replace the relative urls for the images with %bind(:#) variables.

/*
    Colorbox Core Style:
    The following CSS is consistent between example themes and should not be altered.
*/
#colorbox, #cboxOverlay, #cboxWrapper{position:absolute; top:0; left:0; z-index:9999; overflow:hidden;}
#cboxWrapper {max-width:none;}
#cboxOverlay{position:fixed; width:100%; height:100%;}
#cboxMiddleLeft, #cboxBottomLeft{clear:left;}
#cboxContent{position:relative;}
#cboxLoadedContent{overflow:auto; -webkit-overflow-scrolling: touch;}
#cboxTitle{margin:0;}
#cboxLoadingOverlay, #cboxLoadingGraphic{position:absolute; top:0; left:0; width:100%; height:100%;}
#cboxPrevious, #cboxNext, #cboxClose, #cboxSlideshow{cursor:pointer;}
.cboxPhoto{float:left; margin:auto; border:0; display:block; max-width:none; -ms-interpolation-mode:bicubic;}
.cboxIframe{width:100%; height:100%; display:block; border:0; padding:0; margin:0;}
#colorbox, #cboxContent, #cboxLoadedContent{box-sizing:content-box; -moz-box-sizing:content-box; -webkit-box-sizing:content-box;}

/* 
    User Style:
    Change the following styles to modify the appearance of Colorbox.  They are
    ordered & tabbed in a way that represents the nesting of the generated HTML.
*/
#cboxOverlay{background:#000;}
#colorbox{outline:0;}
    #cboxTopLeft{width:21px; height:21px; background:url(%BIND(:2)) no-repeat -101px 0;}
    #cboxTopRight{width:21px; height:21px; background:url(%bind(:2)) no-repeat -130px 0;}
    #cboxBottomLeft{width:21px; height:21px; background:url(%bind(:2)) no-repeat -101px -29px;}
    #cboxBottomRight{width:21px; height:21px; background:url(%bind(:2)) no-repeat -130px -29px;}
    #cboxMiddleLeft{width:21px; background:url(%bind(:2)) left top repeat-y;}
    #cboxMiddleRight{width:21px; background:url(%bind(:2)) right top repeat-y;}
    #cboxTopCenter{height:21px; background:url(%bind(:3)) 0 0 repeat-x;}
    #cboxBottomCenter{height:21px; background:url(%bind(:3)) 0 -29px repeat-x;}
    #cboxContent{background:#fff; overflow:hidden;}
        .cboxIframe{background:#fff;}
        #cboxError{padding:50px; border:1px solid #ccc;}
        #cboxLoadedContent{margin-bottom:28px;}
        #cboxTitle{position:absolute; bottom:4px; left:0; text-align:center; width:100%; color:#949494;}
        #cboxCurrent{position:absolute; bottom:4px; left:58px; color:#949494;}
        #cboxLoadingOverlay{background:url(%bind(:4)) no-repeat center center;}
        #cboxLoadingGraphic{background:url(images/loading.gif) no-repeat center center;}

        /* these elements are buttons, and may need to have additional styles reset to avoid unwanted base styles */
        #cboxPrevious, #cboxNext, #cboxSlideshow, #cboxClose {border:0; padding:0; margin:0; overflow:visible; width:auto; background:none; }
        
        /* avoid outlines on :active (mouseclick), but preserve outlines on :focus (tabbed navigating) */
        #cboxPrevious:active, #cboxNext:active, #cboxSlideshow:active, #cboxClose:active {outline:0;}

        #cboxSlideshow{position:absolute; bottom:4px; right:30px; color:#0092ef;}
        #cboxPrevious{position:absolute; bottom:0; left:0; background:url(%bind(:2)) no-repeat -75px 0; width:25px; height:25px; text-indent:-9999px;}
        #cboxPrevious:hover{background-position:-75px -25px;}
        #cboxNext{position:absolute; bottom:0; left:27px; background:url(%bind(:2)) no-repeat -50px 0; width:25px; height:25px; text-indent:-9999px;}
        #cboxNext:hover{background-position:-50px -25px;}
        #cboxClose{position:absolute; bottom:0; right:0; background:url(%bind(:2)) no-repeat -25px 0; width:25px; height:25px; text-indent:-9999px;}
        #cboxClose:hover{background-position:-25px -25px;}

/*
  The following fixes a problem where IE7 and IE8 replace a PNG's alpha transparency with a black fill
  when an alpha filter (opacity change) is set on the element or ancestor element.  This style is not applied to or needed in IE9.
  See: http://jacklmoore.com/notes/ie-transparency-problems/
*/
.cboxIE #cboxTopLeft,
.cboxIE #cboxTopCenter,
.cboxIE #cboxTopRight,
.cboxIE #cboxBottomLeft,
.cboxIE #cboxBottomCenter,
.cboxIE #cboxBottomRight,
.cboxIE #cboxMiddleLeft,
.cboxIE #cboxMiddleRight {
    filter: progid:DXImageTransform.Microsoft.gradient(startColorstr=#00FFFFFF,endColorstr=#00FFFFFF);
}

Please note, that I modified the css in example one to use a background of black for the overlay.

#cboxOverlay{background:#000;}

Next copy the "jquery.colorbox.js" into an html object and save it using your local naming standards.  Note, I used the "jquery.colorbox-min" initially but I kept getting JavaScript errors and when I used the file above it worked without issue.

Then create a new weblib record and add the ISCRIPT1 field to your record and save this new definition as a derived work record then add the following code to your ISCRIPT1 FieldFormula PeopleCode and modify the code to match your sites naming standards and image that you have previously imported.  If you use my modified CSS then your Bind variables will match.

Here is my iScript code

Function IScript_colorbox_css   
   &bind1 = %Response.GetImageURL(Image.ZZ_CB_OVERLAY);
   &bind2 = %Response.GetImageURL(Image.ZZ_CB_CONTROLS);
   &bind3 = %Response.GetImageURL(Image.ZZ_CB_BORDER);
   &bind4 = %Response.GetImageURL(Image.ZZ_CB_LOADING_BACKGROUND);
   &bind5 = %Response.GetImageURL(Image.ZZ_CB_LOADING);
   
   %Response.SetContentType("text/css");
   %Response.Write(GetHTMLText(HTML.ZZ_CB_CSS, &bind1, &bind2, &bind3, &bind4, &bind5));
End-Function;



Function IScript_ColorBoxJs
   %Response.SetContentType("text/javascript");
   %Response.Write(GetHTMLText(HTML.ZZ_COLORBOX_JS));
End-Function;

Near Real-time integration without customization

I was speaking with a former colleague, who works at another PeopleSoft shop about integrating current employee's with their talent management system, basically populating current employee demographic data, so they don't have to populate this information when they apply for an internal position.  He told me that he was handed a design that required a nightly batch process to run and identify the changes and send them via integration broker.  

This got us talking about ways to do more real time integration without customizing.  And I shared with him how we use the delivered PERSON_BASIC_SYNC to integrate with our business continuity system, AlertFind.  Our approach was to piggyback off the delivered asynchronous message to generate a synchronous message to AlertFind.

 First you have to make sure you message is active and that you have security defined.

Next you have to configure a Local to Local routing.

Then you need to write a Handler to parse the Local to Local message to get the employee that you are wanting to process out of the message.

This is our Handler:

import ZZ_AF_SEND:UpdateUser;
import PS_PT:Integration:INotificationHandler;



class PersonSync_AlertFind implements PS_PT:Integration:INotificationHandler
   method PersonSync_AlertFind();
   method OnNotify(&_MSG As Message);
end-class;

method PersonSync_AlertFind
end-method;

method OnNotify
   /+ &_MSG as Message +/
   /+ Extends/implements PS_PT:Integration:INotificationHandler.OnNotify +/
   
   Local string &PubNodeName, &LocalNodeName;
   Local Record &idmQueue = CreateRecord(Record.ZZ_IDM_QUEUE);
   Local Record &idmData = CreateRecord(Record.ZZ_IDM);
   
   
   /* process only if message was published on the local node with the INTERNAL version */
   &PubNodeName = &_MSG.PubNodeName;
   SQLExec(SQL.EO_SEL_LOCALNODE, &LocalNodeName);
   If &LocalNodeName = &PubNodeName Then
      
      
      Local string &Emplid;
      &Emplid = &_MSG.GetRowset().GetRow(1).PERSON.EMPLID.Value;
      
      &idmData.EMPLID2.Value = &Emplid;
      &idmData.ZZ_REC_TYPE.Value = "C";
      If &idmData.SelectByKey() Then
         Local ZZ_AF_SEND:UpdateUser &AlertFind = create ZZ_AF_SEND:UpdateUser();
         &AlertFind.arrayOfUsers = CreateArray(&Emplid);
         &AlertFind.Execute();
      End-If;

      
   End-If;
   
end-method;

The handler above instantiates an UpdateUser that takes an array of employee ids, this package handles the message creation and the integration broker call to publish the update, this package is not shown in this blog posting.  

Then configure your handler to be active within the PERSON_BASIC_SYNC.

  This approach is basically asynchronous to synchronous approach that gives our organization near real-time integration with our Business Continuity tool, AlertFind.

Using Pagelet Wizard in Related Content

While at Collaborate 14 this year I was visiting with the wonderful people at WorkStrategy, Inc and discussing their eComp+ product that we happily utilize here at UMB.  While I was there discussing their product they shared with me an interesting request that they frequently get, about seeing all the data from the MSS page without drilling down through the manager's direct reports.  This immediatly made me think of PeopleSoft's Related content, but the dillema was do you write an iScript to format the data specifically for this component or better, could I write one iScript that could be used on many components.  I went for the latter, but that begged the question, do I really want to write a framework that takes data and formats into html to be loaded into the Related Framework iframe?  And then it hit me, could I use Pagelet Wizard to format the HTML?

The problem with Pagelet Wizard is it is designed to give you back the .MAXROWS, so rows 1 through the MAXROWS and it does not take querystring parameters that would be passed by the Related Content Framework.  To handle this I decided to create my own DataSource by extending the PTPPB_PAGELET:DataSource:DataSource by copying the delivered QueryDataSource and adding a "START" parameter.  Using this Parameter I was able to derive a stop row number and modify the XML generation to use a start row number and end row number.  I also limited the number of rows returned by the query to 25000, since it is stated that query's run to rowset of 50000 or more could cause an memory issue with your system.  The reason for the Start Row and End row is simple, I wanted to load the first 100 rows for the query and if the user scrolls the table I wanted to load the next 100 rows.  This allows the service to be responsive and only load data if it is needed.

Below is my DataSource:

import PTPPB_PAGELET:UTILITY:*;

import PTPPB_PAGELET:DataSource:*;

import PTPPB_PAGELET:EXCEPTION:*;



/**

  *  The QueryDataSource retrieves data from a PSQuery and returns it in XML format.

  */

class RLC_QueryDataSource extends PTPPB_PAGELET:DataSource:DataSource

   property string SQL get;

   property PTPPB_PAGELET:UTILITY:Setting QueryName readonly;

  

  

   method RLC_QueryDataSource(&id_param As string);

   method execute() Returns string;

   method Clone() Returns object;

   method initializeSettings(&NewSettings As PTPPB_PAGELET:UTILITY:Collection);

   method processSettingsChange();

   method getSourceDataLinkHTML() Returns string;

   method evaluateOutputFormat() Returns string;

  

private

   instance string &mObjSource;

   instance string &mData;

   instance string &queryLinkURL_inst;

  

   instance array of boolean &arRequiredPrompts;

   method checkQueryForRequiredPrompts(&QuerySelect As ApiObject, &MainQueryPromptColl As ApiObject);

   method checkCriteriaForRequiredPrompts(&Criteria As ApiObject, &MainQueryPromptColl As ApiObject);

  

   Constant &NoTableEdit = 0;

   Constant &TranslateTable = 512;

   Constant &YesNo = 8192;

   Constant &PromptTable = 16384;

  

  

end-class;





/**

  * Constructor

  */

method RLC_QueryDataSource

   /+ &id_param as String +/

  

   %Super = create PTPPB_PAGELET:DataSource:DataSource(&id_param);

   %This.setObjectSubType("QUERY");

   %This.setCanHaveParameters( True);

   %This.initializeSettings(%This.Settings);

   %This.hasSourceDataLinkHTML = True;

  

end-method;



/**

  * Executes the query and returns the results in XML format.

  *

  * @return String XML represenation of the query results.

  */

method execute

   /+ Returns String +/

   /+ Extends/implements PTPPB_PAGELET:DataSource:DataSource.execute +/

   Local ApiObject &MyQuery, &ThisMetaData, &MetaDataColl, &qryPromptColl, &qryPrompt;

   Local number &returnStatus, &i, &j, &ColCount, &RowCount, &StopRow;

   Local string &s_QueryName, &LinkParamList, &s_ParamValue, &s_MaxRows, &TempStr, &dataStr, &s_StartRow;

   Local XmlDoc &xmlDoc;

   Local XmlNode &xmlDocNode, &baseNode, &thisNode, &rowNode;

   Local Field &ThisField, &Prompt;

   Local Record &qryRecord, &promptRecord;

  

   Local PTPPB_PAGELET:UTILITY:Collection &FieldsColl;

   Local PTPPB_PAGELET:DataSource:DataSourceParameter &thisDSParameter;

   Local PTPPB_PAGELET:UTILITY:Parameter &thisOutputParam;

   Local Rowset &rsResult;

   Local Row &QryRow;

   Local array of string &arQueryFields, &arQueryFldHeadings;

  

   &s_QueryName = %This.ParameterCollection.getItemByID("QueryName").evaluatedValue();

  

   &MyQuery = %Session.GetQuery();

   If &MyQuery = Null Then

      throw create PTPPB_PAGELET:EXCEPTION:QueryException(&s_QueryName);

   End-If;

   &returnStatus = &MyQuery.Open(&s_QueryName, True, False);

   If &returnStatus <> 0 Then

      /* Error opening query */

      throw create PTPPB_PAGELET:EXCEPTION:QueryException(&s_QueryName);

   End-If;

   &qryPromptColl = &MyQuery.runtimeprompts;

   &promptRecord = &MyQuery.promptrecord;

  

   If &promptRecord <> Null Then

      If %This.ParameterCollection.Count > 0 Then

         /* bind the prompt record with the query */

        

         For &i = 1 To &promptRecord.FieldCount

            &Prompt = &promptRecord.GetField(&i);

            &qryPrompt = &qryPromptColl.item(&i);

           

            /* Retrieve the datasource parameter for this prompt.  First, try using the explicit format... */

            /*   Note: the explicit format will only be used when a field is used more than once in  */

            /*         the prompts */

            &thisDSParameter = %This.ParameterCollection.getItemByID(&qryPrompt.Name | "." | &i);

            If &thisDSParameter = Null Then

               /* Did not find in explicit format ... try just the fieldname */

               /* Note:  this is the most common case */

               &thisDSParameter = %This.ParameterCollection.getItemByID(&qryPrompt.Name);

            End-If;

           

            Evaluate &Prompt.Type

            When "DATETIME"

               &thisDSParameter.FieldType = &thisDSParameter.FIELDTYPE_DATETIME;

               &Prompt.Value = DateTimeValue(&thisDSParameter.evaluatedValue());

               Break;

            When "DATE"

               &thisDSParameter.FieldType = &thisDSParameter.FIELDTYPE_DATE;

               &Prompt.Value = DateValue(&thisDSParameter.evaluatedValue());

               Break;

            When "TIME"

               &thisDSParameter.FieldType = &thisDSParameter.FIELDTYPE_TIME;

               &Prompt.Value = TimeValue(&thisDSParameter.evaluatedValue());

               Break;

            When-Other

              

               &Prompt.Value = &thisDSParameter.evaluatedValue();

            End-Evaluate;

            &LinkParamList = &LinkParamList | "&" | EncodeURLForQueryString(&qryPrompt.uniquepromptname) | "=" | EncodeURLForQueryString(&Prompt.Value);

         End-For;

      End-If;

     

   End-If;

  

   &s_MaxRows = %This.ParameterCollection.getItemByID(".MAXROWS").Value;

   try

      &s_StartRow = %This.ParameterCollection.getItemByID("START").Value;

   catch Exception &EXCEPTIION

      &s_StartRow = "1";

   end-try;

   If &s_MaxRows = "" Then

      &s_MaxRows = %This.ParameterCollection.getItemByID(".MAXROWS").DefaultValue;

   End-If;

   rem &rsResult = &MyQuery.RunTorowset(&promptRecord, Value(&s_MaxRows));

   REM SET TO A MAX OF 25000;

   &rsResult = &MyQuery.RunTorowset(&promptRecord, 25000);

   If &rsResult = Null Then

      throw create PTPPB_PAGELET:EXCEPTION:QueryException(&s_QueryName);

   End-If;

  

   /* Create the xml output from the query ... */

   &xmlDoc = CreateXmlDoc("<?xml version='1.0' encoding='UTF-8' standalone='yes'?><queryresult/>");

   &xmlDocNode = &xmlDoc.DocumentElement;

  

   &baseNode = &xmlDocNode.AddElement("queryproperties");

   &thisNode = &baseNode.AddElement("querylink");

   &thisNode = &thisNode.AddElement("URL");

  

   &queryLinkURL_inst = GenerateQueryPortalURL(%Portal, %Node, &s_QueryName, True) | &LinkParamList;

  

   &thisNode.NodeValue = &queryLinkURL_inst;

  

  

  

   &thisNode = &baseNode.AddElement("queryname");

   &thisNode.NodeValue = &s_QueryName;

  

   &thisNode = &baseNode.AddElement("querydescription");

   &thisNode.NodeValue = EscapeHTML(&MyQuery.Description);

  

   &thisNode = &baseNode.AddElement("pagelet_width");

   If %Request.GetParameter("PORTALPARAM_COMPWIDTH") = "Wide" Then

      &thisNode.NodeValue = "Wide";

   Else

      &thisNode.NodeValue = "Narrow";

   End-If;

  

  

   &QryRow = &rsResult.GetRow(1);

   &qryRecord = &QryRow.GetRecord(1);

   &ColCount = &qryRecord.FieldCount;

  

   &baseNode = &xmlDocNode.AddElement("queryfields");

   &baseNode.AddAttribute("numfields", String(&ColCount));

  

   &FieldsColl = %This.OutputFields;

  

   If &FieldsColl.Count = 0 Then

      /* OutputFields not set, get the fields collection manually */

     

      &FieldsColl = create PTPPB_PAGELET:UTILITY:Collection("FieldsCollection");

      &FieldsColl.setDuplicatesAllowed();

      &MetaDataColl = &MyQuery.metadata;

      For &i = 1 To &MetaDataColl.count

         &ThisMetaData = &MetaDataColl.item(&i);

         Evaluate &ThisMetaData.name

         When "Field"

            &TempStr = &ThisMetaData.value;

            If &arQueryFields = Null Then

               &arQueryFields = CreateArray(&TempStr);

            Else

               &arQueryFields.Push(&TempStr);

            End-If;

            Break;

         When "Heading"

           

            &TempStr = &ThisMetaData.value;

            If &arQueryFldHeadings = Null Then

               &arQueryFldHeadings = CreateArray(&TempStr);

            Else

               &arQueryFldHeadings.Push(&TempStr);

            End-If;

            <**>

           

            Break;

         End-Evaluate;

      End-For;

     

      For &j = 1 To &arQueryFields.Len

         &thisOutputParam = create PTPPB_PAGELET:UTILITY:Parameter(&arQueryFields [&j]);

        

         &thisOutputParam.Value = &arQueryFldHeadings [&j];

        

        

         &FieldsColl.Insert(&thisOutputParam);

      End-For;

      &FieldsColl.setImmutable();

   End-If;

  

   For &j = 1 To &qryRecord.FieldCount

      &thisNode = &baseNode.AddElement("field");

      &thisNode.AddAttribute("fieldname", &FieldsColl.getItem(&j).Name);

      &thisNode = &thisNode.AddElement("headingtext");

      &thisNode.NodeValue = &FieldsColl.getItem(&j).Value;

   End-For;

  

   &RowCount = &rsResult.RowCount - 1;

   rem -  one extra row gets appended at the end of the rowset ;

   If &RowCount > 0 Then

      &baseNode = &xmlDocNode.AddElement("queryrows");

      &baseNode.AddAttribute("numrows", String(&RowCount));

      If Value(&s_StartRow) = 1 Then

         &StopRow = Value(&s_MaxRows);

      Else

         &StopRow = Value(&s_MaxRows) + Value(&s_StartRow) - 1;

      End-If;

      rem this is a partial return of data stop at the rowcount;

      If &StopRow > &RowCount Then

         &StopRow = &RowCount

      End-If;

     

      For &i = Value(&s_StartRow) To &StopRow

         &QryRow = &rsResult(&i);

         &rowNode = &baseNode.AddElement("row");

         &rowNode.AddAttribute("rownumber", String(&i));

         For &j = 1 To &qryRecord.FieldCount

            &thisNode = &rowNode.AddElement("querydata");

            &thisNode.AddAttribute("fieldname", &FieldsColl.getItem(&j).Name);

           

            &dataStr = &QryRow.GetRecord(1).GetField(&j).Value;

           

            &thisNode = &thisNode.AddElement("text");

            &thisNode.NodeValue = &dataStr;

         End-For;

      End-For;

   End-If;

  

   Return &xmlDoc.GenXmlString();

  

end-method;



/**

  * Initializes the QueryName setting. 

  *

  * @param NewSettings Collection of settings to use with this pagelet.  A new collection is created if null.

  */

method initializeSettings

   /+ &NewSettings as PTPPB_PAGELET:UTILITY:Collection +/

   /+ Extends/implements PTPPB_PAGELET:DataSource:DataSource.initializeSettings +/

   Local PTPPB_PAGELET:UTILITY:Setting &thisSetting;

  

   If &NewSettings = Null Then

      &NewSettings = create PTPPB_PAGELET:UTILITY:Collection("QueryDataSourceSettings");

   End-If;

   %This.setSettings(&NewSettings);

  

   &thisSetting = %This.Settings.getItemByID("QueryName");

   If &thisSetting = Null Then

      &QueryName = %This.createSettingProperty("QueryName", "");

   Else

      &QueryName = &thisSetting;

      %This.setSettingsComplete( True);

   End-If;

   &QueryName.PromptTable = Record.PTPPB_QUERY_VW;

   &QueryName.EditType = &QueryName.EDITTYPE_PROMPTTABLE;

   &QueryName.FieldType = &QueryName.FIELDTYPE_CHARACTER;

   &QueryName.Enabled = True;

   &QueryName.Visible = True;

   &QueryName.RefreshOnChange = True;

   &QueryName.Required = True;

   &QueryName.LongName = MsgGetText(219, 401, "(Message Not Found)Query Name");

  

  

   &QueryName.setObjectToRefreshOnValueChange(%This);

  

end-method;



/**

  * Handle the changing of the QueryName Setting value. 

  *

  * <P>NOTE: CHANGING THE VALUE OF MANY SETTINGS WILL CAUSE THE PARAMETERCOLLECTION

  * TO BE REBUILT.  IT IS VERY VERY IMPORTANT THAT THE PARAMETERCOLLECTION

  * BE SET (OR RETRIEVED) AFTER ALL SETTINGS VALUES HAVE BEEN SET!!!</P>

  */

method processSettingsChange

   /+ Extends/implements PTPPB_PAGELET:DataSource:DataSource.processSettingsChange +/

   Local string &s_QueryName;

   Local PTPPB_PAGELET:UTILITY:Setting &querySetting;

   Local PTPPB_PAGELET:UTILITY:Collection &Params, &Outputs;

   Local PTPPB_PAGELET:DataSource:DataSourceParameter &thisParam;

   Local PTPPB_PAGELET:UTILITY:OutputField &thisOutputParam;

   Local number &i, &j;

  

   Local number &returnStatus;

   Local string &DataSourceName, &TempStr;

   Local ApiObject &MyQuery, &qryPrompt, &ThisMetaData, &MetaDataColl;

   Local string &sFieldVal, &sLongname, &sShortname;

   Local number &nXlat_cnt;

   Local array of array of string &arValidSet;

   Local array of string &arQueryFields;

   Local array of string &arQueryFldHeadings;

   Local array of string &temparray;

   Local ApiObject &qryPromptColl;

  

   /* do nothing if no settings */

   If %This.Settings = Null Then

      /* Mark the settings as incomplete */

      %This.setSettingsComplete( False);

      Return;

   End-If;

  

   &querySetting = %This.Settings.getItemByID("QueryName");

   /* do nothing if no queryname setting */

   If &querySetting = Null Then

      /* Mark the settings as incomplete */

      %This.setSettingsComplete( False);

      Return

   End-If;

  

   &s_QueryName = &querySetting.Value;

   /* do nothing if the queryname is blank */

   If &s_QueryName = "" Then

      /* Mark the settings as incomplete */

      %This.setSettingsComplete( False);

      Return;

   End-If;

  

   /* Looks like we have a queryname - now create the ParameterCollection*/

   &Params = %This.createEmptyParameterCollection();

  

   /* Look up the query, and copy the binds into the ParameterCollection */

   Local Rowset &rsXlat = CreateRowset(Record.PSXLATITEM);

   &MyQuery = %Session.GetQuery();

   If &MyQuery = Null Then

      throw create PTPPB_PAGELET:EXCEPTION:QueryException(&QueryName.Value);

   End-If;

   &returnStatus = &MyQuery.Open(&s_QueryName, True, False);

   If &returnStatus <> 0 Then

      throw create PTPPB_PAGELET:EXCEPTION:QueryException(&QueryName.Value);

   End-If;

   &qryPromptColl = &MyQuery.runtimeprompts;

  

   /* Mark all prompts as not required... then call the checkQueryForRequiredPrompts method

       to determine which are in fact required. */

   &arRequiredPrompts = CreateArrayRept( False, &qryPromptColl.count);

   %This.checkQueryForRequiredPrompts(&MyQuery.QuerySelect, &qryPromptColl);

  

   For &i = 1 To &qryPromptColl.Count

      &qryPrompt = &qryPromptColl.item(&i);

     

      /* First, see if a parameter has already been created with this field name */

      &thisParam = %This.ParameterCollection.getItemByID(&qryPrompt.Name);

      If &thisParam = Null Then

         /* No parameter with this field name exists ... just use the field name for the parameter name */

         /* Note:  This is the most common case */

         &thisParam = create PTPPB_PAGELET:DataSource:DataSourceParameter(&qryPrompt.name);

      Else

         /* A parameter exists with this field name ... use an explicit format */

         /* Note: this is only used when a field is used more than once in the prompt list */

         &thisParam = create PTPPB_PAGELET:DataSource:DataSourceParameter(&qryPrompt.name | "." | &i);

      End-If;

     

      &thisParam.LongName = &qryPrompt.headingtext;

      &thisParam.DefaultValue = "";

      rem &thisParam.required=true;

      &thisParam.Required = &arRequiredPrompts [&i];

      &thisParam.allowChangesToRequired = ( Not (&arRequiredPrompts [&i]));

     

      &thisParam.FieldType = &qryPrompt.FieldType;

      Evaluate &qryPrompt.editType

      When 1

      When &NoTableEdit

         &thisParam.EditType = 1;

         Break;

      When 2

      When &PromptTable

         &thisParam.EditType = 2;

         Break;

      When 3

      When &TranslateTable

         &thisParam.EditType = 3;

         Break;

      When 4

      When &YesNo

         &thisParam.EditType = 4;

         Break;

      When-Other

         throw create PTPPB_PAGELET:EXCEPTION:InvalidValueException(&thisParam.Name | ".FieldType", String(&thisParam.FieldType));

      End-Evaluate;

     

      If &thisParam.EditType = 2 Then

         &thisParam.PromptTable = &qryPrompt.PromptTable;

      End-If;

     

      If &thisParam.EditType = 3 Then

         &thisParam.setRangeFromFieldTranslates(&qryPrompt.name);

      End-If;

     

      &Params.Insert(&thisParam);

   End-For;

  

   /* Also set up the OutputFields */

   &Outputs = create PTPPB_PAGELET:UTILITY:Collection("OutputFields");

   &MetaDataColl = &MyQuery.metadata;

   For &i = 1 To &MetaDataColl.count

      &ThisMetaData = &MetaDataColl.item(&i);

      Evaluate &ThisMetaData.name

      When "Field"

         &TempStr = &ThisMetaData.value;

         If &arQueryFields = Null Then

            &arQueryFields = CreateArray(&TempStr);

         Else

            &arQueryFields.Push(&TempStr);

         End-If;

         Break;

      When "Heading"

         &TempStr = &ThisMetaData.value;

         If &arQueryFldHeadings = Null Then

            &arQueryFldHeadings = CreateArray(&TempStr);

         Else

            &arQueryFldHeadings.Push(&TempStr);

         End-If;

        

         Break;

      End-Evaluate;

   End-For;

  

   For &j = 1 To &arQueryFields.Len

      /* First, see if an output has already been created with this field name */

      &thisOutputParam = &Outputs.getItemByID(&arQueryFields [&j]);

      If &thisOutputParam = Null Then

         /* No parameter with this field name exists ... just use the field name for the parameter name */

         /* Note:  This is the most common case */

         &thisOutputParam = create PTPPB_PAGELET:UTILITY:OutputField(&arQueryFields [&j]);

      Else

         /* A parameter exists with this field name ... use an explicit format */

         /* Note: this is only used when a field is used more than once in the prompt list */

         &thisOutputParam = create PTPPB_PAGELET:UTILITY:OutputField(&arQueryFields [&j] | "." | &j);

      End-If;

      &thisOutputParam.Value = &arQueryFldHeadings [&j];

      &thisOutputParam.HeadingXPATH = "xpath:/queryresult/queryfields/field[position()=" | &j | "]/headingtext";

      &thisOutputParam.RowDataXPATH = "xpath:querydata[position()=" | &j | "]/text";

      &Outputs.Insert(&thisOutputParam);

   End-For;

  

   %This.setOutputFields(&Outputs);

  

   /* Add the reserved .MAXROWS parameter to the collection */

   &thisParam = create PTPPB_PAGELET:DataSource:DataSourceParameter(".MAXROWS");

   &thisParam.LongName = MsgGetText(219, 252, "Message Not Found - Max Rows");

   &thisParam.DefaultValue = "10";

   &thisParam.Required = True;

   &thisParam.FieldType = &thisParam.FIELDTYPE_NUMBER;

   &Params.Insert(&thisParam);

  

   /* Lastly - add in the queryname as an internal parameter */

   &thisParam = create PTPPB_PAGELET:DataSource:DataSourceParameter("QueryName");

   &thisParam.Value = &s_QueryName;

   &thisParam.UsageType = &thisParam.USAGETYPE_INTERNAL;

   &Params.Insert(&thisParam);

  

   /* and set the ParameterCollection to be immutable */

   &Params.setImmutable();

  

   /* Mark the settings as complete */

   %This.setSettingsComplete( True);

  

end-method;



/**

  * Makes an exact duplicate of this QueryDataSource

  *

  *@return Object Copy of this QueryDataSource

  */

method Clone

   /+ Returns Object +/

   /+ Extends/implements PTPPB_PAGELET:DataSource:DataSource.Clone +/

   Local PTPPB_PAGELET:DataSource:QueryDataSource &NewDS = create PTPPB_PAGELET:DataSource:QueryDataSource(%This.ID);

   &NewDS.PageletID = %This.PageletID;

   &NewDS.ParameterCollection = %This.ParameterCollection.Clone();

   &NewDS.initializeSettings(%This.Settings.Clone());

   Return &NewDS;

end-method;



/**

  * Returns the SQL for the Query

  *

  * @return String SQL for the Query

  */

get SQL

   /+ Returns String +/

   Local ApiObject &MySession, &QryObj;

   Local string &SelectStatement;

   Local string &DataSourceName;

   Local number &returnStatus;

  

   &QryObj = %Session.getquery();

   If &QryObj = Null Then

      throw create PTPPB_PAGELET:EXCEPTION:QueryException(&QueryName.Value);

   End-If;

   &DataSourceName = &QueryName.Value;

   &returnStatus = &QryObj.Open(&DataSourceName, True, False);

   If &returnStatus <> 0 Then

      throw create PTPPB_PAGELET:EXCEPTION:QueryException(&QueryName.Value);

   End-If;

   &SelectStatement = &QryObj.sql;

   Return &SelectStatement;

end-get;



/**

  * Generates an HTML link to execute the full PSQuery

  *

  * @return String URL for the full query

  */

method getSourceDataLinkHTML

   /+ Returns String +/

   /+ Extends/implements PTPPB_PAGELET:DataSource:DataSource.getSourceDataLinkHTML +/

   Local XmlNode &thisNode;

   Local XmlNode &tableNode, &trNode, &tdNode, &queryLinkNode;

  

   Local string &queryLinkText = MsgGetText(219, 402, "Full Query Results (Message Not Found)");

   Local XmlDoc &XMLDoc = CreateXmlDoc("<?xml version='1.0' standalone='yes'?><TABLE/>");

   &tableNode = &XMLDoc.DocumentElement;

  

   &tableNode.AddAttribute("width", "94%");

   &tableNode.AddAttribute("border", "0");

   &trNode = &tableNode.AddElement("TR");

   &tdNode = &trNode.AddElement("TD");

   &queryLinkNode = &tdNode.AddElement("A");

   &queryLinkNode.AddAttribute("class", "PSHYPERLINK");

   &queryLinkNode.AddAttribute("target", "_top");

   &queryLinkNode.AddAttribute("href", &queryLinkURL_inst);

   &thisNode = &queryLinkNode.AddText(&queryLinkText);

  

   Return &tableNode.GenXmlString();

end-method;



/**

  * Determines the outputformat and sets hasXPATHInfo to true

  *

  * @return String "QUERY"

  */

method evaluateOutputFormat

   /+ Returns String +/

   /+ Extends/implements PTPPB_PAGELET:DataSource:DataSource.evaluateOutputFormat +/

   %This.hasXPATHInfo = True;

   %This.BuilderContextNode = "/queryresult/queryrows/row";

   Return %This.OUTPUTFORMAT_PSQUERY;

end-method;



/*********************************

  PRIVATE METHODS FOLLOW

 *********************************/



/*

 * checkQueryForRequiredPrompts and checkCriteriaForRequiredPrompts

 *

 * Recursive methods that checks the query prompts, looking for all prompts that must

 *   be marked required.  Essentially, any prompt found not to be in an IN or NOT IN

 *   criteria should be marked required.

 */

method checkQueryForRequiredPrompts

   /+ &QuerySelect as ApiObject, +/

   /+ &MainQueryPromptColl as ApiObject +/

   Local ApiObject &criteria, &QuerySelectColl, &thisSQ;

  

   &criteria = &QuerySelect.criteria;

   %This.checkCriteriaForRequiredPrompts(&criteria, &MainQueryPromptColl);

  

   &criteria = &QuerySelect.havingcriteria;

   %This.checkCriteriaForRequiredPrompts(&criteria, &MainQueryPromptColl);

  

   &QuerySelectColl = &QuerySelect.QuerySelects;

   &thisSQ = &QuerySelectColl.first();

   While &thisSQ <> Null

      %This.checkQueryForRequiredPrompts(&thisSQ, &MainQueryPromptColl);

      &thisSQ = &QuerySelectColl.next();

   End-While;

  

end-method;



method checkCriteriaForRequiredPrompts

   /+ &Criteria as ApiObject, +/

   /+ &MainQueryPromptColl as ApiObject +/

  

   Local ApiObject &thiscriteria, &subquery;

   Local number &numPrompts, &promptID, &i;

  

   &numPrompts = &MainQueryPromptColl.Count;

  

   &thiscriteria = &Criteria.first();

   While &thiscriteria <> Null

      Evaluate &thiscriteria.expr2type

      When %Query_ExprBind

         &promptID = Value(LTrim(&thiscriteria.expr2constant1, ":"));

         &arRequiredPrompts [&promptID] = True;

         Break;

        

      When %Query_Expression

      When %Query_ExprExprConst

      When %Query_ExprExprFld

      When %Query_ExprBothExpr

         If &thiscriteria.Expr2Expression1.BindFlag = True Then

            For &i = 1 To &arRequiredPrompts.Len

               If Find(":" | String(&i) | " ", &thiscriteria.Expr2Expression1.Text) > 0 Or

                     Find(":" | String(&i), &thiscriteria.Expr2Expression1.Text) > Len(&thiscriteria.Expr2Expression1.Text) - Len(":" | String(&i)) Then

                  &arRequiredPrompts [&i] = True;

               End-If;

            End-For;

         End-If;

         Break;

        

      When %Query_ExprConstExpr

      When %Query_ExprFldExpr

      When %Query_ExprBothExpr

         If &thiscriteria.Expr2Expression2.BindFlag = True Then

            For &i = 1 To &arRequiredPrompts.Len

               If Find(":" | String(&i) | " ", &thiscriteria.Expr2Expression2.Text) > 0 Or

                     Find(":" | String(&i), &thiscriteria.Expr2Expression2.Text) > Len(&thiscriteria.Expr2Expression1.Text) - Len(":" | String(&i)) Then

                  &arRequiredPrompts [&i] = True;

               End-If;

            End-For;

         End-If;

        

      End-Evaluate;

     

      &thiscriteria = &Criteria.next();

   End-While;

  

end-method;

Next I configured my new Data Type so I could use it in Pagelet Wizard.

As I stated earlier, Pagelet Wizard cannot take a Querystring paramter and this is how the Related Content Framework passes parameters to it's services.  So I created a new record called WEBLIB_ZZ_RLC and added the ISCRIPT1 field and the code below to the FieldFormula.

import PTPPB_PAGELET:*;

import PTPPB_PAGELET:DataSource:*;

import ZZ_JSON:JSONEncoder;

Local XmlDoc &xmlDoc;

Function IScript_RLC_CSS()

   %Response.SetContentType("text/css");

   %Response.Write(GetHTMLText(HTML.ZZ_RLC_SWAN_CSS));

End-Function;

Function json()

   Local array of XmlNode &rows = &xmlDoc.DocumentElement.FindNodes("/root/table/tbody/tr/td/table/tbody/tr");

   Local array of XmlNode &td;

   Local ZZ_JSON:JSONEncoder &utility = create ZZ_JSON:JSONEncoder();

   Local boolean &isFirstRow = True;

   

   %Response.Write("[");

   rem start on row two to avoid the table headers;

   For &x = 2 To &rows.Len

      If (&isFirstRow) Then

         &isFirstRow = False;

      Else

         %Response.Write(", ");

      End-If;

      %Response.Write("{""trClass"": """ | &utility.encode(&rows [&x].GetAttributeValue("class")) | """, ""valign"": """ | &utility.encode(&rows [&x].GetAttributeValue("valign")) | """,  ""td"": [");

      &td = &rows [&x].FindNodes("td");

      &isFirstData = True;

      For &y = 1 To &td.Len

         If (&isFirstData) Then

            &isFirstData = False;

         Else

            %Response.Write(", ");

         End-If;

         &Anchor = &td [&y].FindNode("A");

         If &Anchor.isnull Then

            %Response.Write("""" | &utility.encode(&td [&y].NodeValue) | """");

         Else

            %Response.Write("{""href"": """ | &utility.encode(&Anchor.GetAttributeValue("href")) | """, ""title"": """ | &utility.encode(&Anchor.GetAttributeValue("title")) | """,  ""value"": """ | &Anchor.nodevalue | """}");

         End-If;

      End-For;

      %Response.Write("]}");

   End-For;

   %Response.Write("]");

End-Function;

Function iScript_Request()

   Local array of string &bindArray = CreateArray(%Request.GetParameter("BIND1"), %Request.GetParameter("BIND2"), %Request.GetParameter("BIND3"), %Request.GetParameter("BIND4"), %Request.GetParameter("BIND5"), %Request.GetParameter("BIND6"), %Request.GetParameter("BIND7"), %Request.GetParameter("BIND8"), %Request.GetParameter("BIND9"), %Request.GetParameter("BIND10"));

   Local array &dsParms = CreateArray();

   Local string &START = %Request.GetParameter("START");

   Local string &AJAX = %Request.GetParameter("AJAX");

   Local string &PAGELETID = %Request.GetParameter("PAGELETID");

   Local number &chunk = 100;

   

   Local PTPPB_PAGELET:PageletWizard &PWAPI = create PTPPB_PAGELET:PageletWizard();

   

   Local PTPPB_PAGELET:Pagelet &thisPagelet = &PWAPI.getPageletByID(&PAGELETID, True);

   

   

   SQLExec("select PTPPB_VALUE from ps_PTPPB_PGLT_PRMS where PTPPB_PAGELET_ID = :1 and PTPPB_FIELDNAME = 'QueryName'", &PAGELETID, &QryName);

   

   &ajaxQS = "?AJAX=Y&PAGELETID=" | &PAGELETID;

   For &x = 1 To &bindArray.Len

      If All(&bindArray [&x]) Then

         Local string &thisBind = "BIND" | String(&x);

         SQLExec("SELECT FIELDNAME||'.'||BNDNUM FROM PSQRYBIND WHERE OPRID = ' ' AND QRYNAME = :1 AND BNDNAME=:2", &QryName, &thisBind, &qryBind);

         REM MAP BIND VARIABLES TO THE RLC QUERY DATASOURCE PARAMETERS;

         Local PTPPB_PAGELET:DataSource:DataSourceParameter &Temp = &thisPagelet.DataSource.addParameter(&qryBind, &bindArray [&x]);;

         &dsParms.Push(&Temp);

         &ajaxQS = &ajaxQS | "&" | &thisBind | "=" | &bindArray [&x];

      End-If;

   End-For;

   

   

   &loadURL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_RLC, Field.ISCRIPT1, "FieldFormula", "iScript_Request") | &ajaxQS | "&START=";

   &cssURL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_ZZ_RLC, Field.ISCRIPT1, "FieldFormula", "IScript_RLC_CSS");

   &jQueryURL = GenerateScriptContentURL(%Portal, Node.HRMS, Record.WEBLIB_APT_JSL, Field.ISCRIPT1, "FieldFormula", "IScript_jQuery_1_7_1");

   

   If &AJAX <> "Y" Then

      &AJAX = "N";

      Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsStart = &thisPagelet.DataSource.addParameter("START", "1");

   Else

      Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsStartAJAX = &thisPagelet.DataSource.addParameter("START", &START);

      %Response.SetContentType("application/json");

   End-If;

   

   Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsParmq = &thisPagelet.DataSource.addParameter("QueryName", &QryName);

   Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsParmR = &thisPagelet.DataSource.addParameter(".MAXROWS", String(&chunk));

   Local PTPPB_PAGELET:DataSource:DataSourceParameter &dsParmR1 = &thisPagelet.DataSource.addParameter("AJAX", &AJAX);

   

   &string = &thisPagelet.Execute();

   

   

   &html = GetHTMLText(HTML.ZZ_RLC_REQUEST, &loadURL, &string, &chunk, &cssURL, &jQueryURL);

   

   

   If &AJAX = "N" Then

      

      %Response.Write(&html);

      

   Else

      REM DO JSON PROCESSING;

      &xmlDoc = CreateXmlDoc("<?xml version='1.0'?><root>" | &string | "</root>");

      

      json();

      

   End-If;

   

End-Function;

Lets take a look at this WEBLIB.  First you are going to need to inject jQuery, I used version 1.7.1 which I have stored in an HTML object, if you don't know how to do this, then please search google for "serving jquery in Peoplesoft".  The  Import ZZ_JSON:JSONEncoder documentation can be found on Jim Marion's blog. Third, I added 10 get parameters to get values passed into this weblib from the Related Content Framework.  You can add more, but I thought 10 will do.  Fourth, I  created the DataSource parameters for the query within this WEBLIB, this prevents it from happening within the DataSource itself, very clever Oracle! Fifth, I add a style sheet to the embedded Pagelet to give it some style.   This is the CSS stored in the ZZ_RLC_SWAN_CSS:

.PSLEVEL1GRIDODDROW,.PSLEVEL2GRIDODDROW,.PSLEVEL3GRIDODDROW,.PSSRCHRESULTSODDROW{font-family:Arial,sans-serif;font-size:9pt;font-weight:normal;font-style:normal;color:rgb(60,60,60);background-color:rgb(247,247,247);border-bottom-width:1px;border-right-width:1px;border-bottom-color:rgb(203,203,203);border-right-color:rgb(203,203,203);border-bottom-style:solid;border-right-style:solid;}

.PSLEVEL1GRIDEVENROW,.PSLEVEL2GRIDEVENROW,.PSLEVEL3GRIDEVENROW,.PSSRCHRESULTSEVENROW{font-family:Arial,sans-serif;font-size:9pt;font-weight:normal;font-style:normal;color:rgb(60,60,60);background-color:rgb(255,255,255);border-bottom-width:1px;border-right-width:1px;border-bottom-color:rgb(203,203,203);border-right-color:rgb(203,203,203);border-bottom-style:solid;border-right-style:solid;}

.PSLEVEL1GRIDCOLUMNHDRTXT,.PSLEVEL3GRIDCOLUMNHDRTXT,.PSLEVEL1GRIDCOLUMNHDR:LINK,.PSLEVEL1GRIDCOLUMNHDR:VISITED,.PSLEVEL1GRIDCOLUMNHDRDISABLED,.PSLEVEL1SSGRIDCOLUMNHDR:LINK,.PSLEVEL1SSGRIDCOLUMNHDR:VISITED,.PSLEVEL1SSGRIDCOLUMNHDRDISABLED,.PSLEVEL3GRIDCOLUMNHDR:LINK,.PSLEVEL3GRIDCOLUMNHDR:VISITED,.PSLEVEL3GRIDCOLUMNHDRDISABLED{font-family:Arial,sans-serif;font-size:8pt;font-weight:bold;font-style:normal;color:rgb(51,102,153);background-color:rgb(223,231,236);border-top-style:none;border-bottom-style:none;border-left-style:none;border-right-style:none;}

.PSLEVEL1GRIDCOLUMNHDR,.PSLEVEL2GRIDCOLUMNHDR,.PSLEVEL3GRIDCOLUMNHDR{font-family:Arial,sans-serif;font-size:8pt;font-weight:bold;font-style:normal;color:rgb(51,102,153);background-color:rgb(223,231,236);border-top-width:1px;border-bottom-width:1px;border-right-width:1px;border-top-color:rgb(203,203,203);border-bottom-color:rgb(203,203,203);border-right-color:rgb(203,203,203);border-top-style:solid;border-bottom-style:solid;border-right-style:solid;}

.PSGRIDCOLUMNHDR{font-family:Arial,sans-serif;font-size:8pt;font-weight:bold;font-style:normal;color:rgb(51,102,153);background-color:rgb(223,231,236);border-top-width:1px;border-bottom-width:1px;border-top-color:rgb(163,190,216);border-bottom-color:rgb(163,190,216);border-top-style:solid;border-bottom-style:solid;}

.PSDROPDOWNLIST,.PSLEVEL1GRIDROW,.PSLEVEL2GRIDROW,.PSLEVEL3GRIDROW,.PSLONGEDITBOX,.PSSRCHINSTRUCTIONS,.PSSRCHRESULTSFOOTER,.PSTEXT,.PSQRYINSTRUCTIONS,.PSQRYRESULTSFOOTER,.PT_MCFDROPDOWNLIST,.PT_MCFEDITBOX{font-family:Arial,sans-serif;font-size:9pt;font-weight:normal;font-style:normal;color:rgb(60,60,60);}

The json() function is for AJAX calls back to the service so it can load the next 100 rows as the user scrolls. The JavaScript for this behavior is in the HTML.ZZ_RLC_REQUEST. This is documented below:

<script type="text/javascript"  src="%BIND(:5)"></script>
<script type="text/javascript">
function importStylesheet(sheetUrl) {
  var ss = document.createElement("link");
  ss.rel = "stylesheet";
  ss.href = sheetUrl;
  ss.type = "text/css";
  document.getElementsByTagName("head")[0].appendChild(ss);
}
  importStylesheet("%bind(:4)");

 
var processing;
var chunk = %bind(:3);
var StartRow = chunk + 1;
var iScriptURL = "%bind(:1)"; 
$(document).ready(function(){
      
    $(window).scroll(function(e){
         
        if (processing)
            return false;

        if ($(window).scrollTop() >= ($(document).height() - $(window).height())*0.7){
          processing = true;
           iScriptURL2 = iScriptURL+StartRow;
            
            $.get(iScriptURL2, function(loaded){
var htmlRows = '';
StartRow = StartRow + chunk;
for (var i=0;i<loaded.length;i++)
{ 
  //debugger;
  htmlRows = htmlRows + "<tr class='"  + loaded[i].trClass + "' valign='" + loaded[i].valign + "'>";
   for (var d=0;d<loaded[i].td.length;d++)
   {
     if ( typeof loaded[i].td[d].href != 'undefined')
        {
         
        
          htmlRows = htmlRows + "<td><a href='" + loaded[i].td[d].href +  "'>" + loaded[i].td[d].value +"</a></td>";
     
           
        }
     else
        {
          htmlRows = htmlRows + "<td>" + loaded[i].td[d] + "</td>";
        }
   }
}
$('.PSLEVEL1GRID tr:last').after(htmlRows);

if (loaded.length>0){
processing = false;
};
});
        };
    });
});
</script>

 
   %bind(:2)

Now all I have to do is create a query and build my Pagelet Wizard definition using the the Related Content Query DataSource, the hidden gems of this approach is that it allowed me to take advandate of Pagelet Wizard's Post-Transformation Processing.  This means I can define query output as hyperlinks and even format the output of the query.  And finally I created my Related Content Service and associated it to the eComp+ Component at the Page Level so I could pass values from the page definition to my iScript.

As for the Parameters, please note that the Parameter Name for the Bind# need to match up with the Query's Bind Name, which typically defaults to BIND1, BIND2 etc....  Also, if you click the link within the Related Content Frame it will replace that frame with the transaction configured within the link.  And lastly, this can be used outside of Related Content, if you properly pass the parameters it could be used as a drilldown in a graph or where ever your imagination takes you!