Determine Languages installed for your PeopleSoft Application

You can use this SQL to find out which languages are already installed for your installation.

select * from PSLANGUAGES where installed = 1

Determine Hidden Folders and Content References for Portal Navigation

If you do not see a folder or content Reference (Menu Item) in Left hand side navigation, (Applicable to 8.4x ) then you must check to see if the folder or content reference is not marked as hidden. Other reasons could be security. Here is a SQL to find out all the objects that are hidden.

To find all the folders which are hidden from Portal Navigation.

select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'F'

select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_label
from PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE' 
and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' 
and b.portal_Reftype = 'F' and a.portal_name = b.portal_name 
and a.portal_objname = b.portal_objname
and b.portal_name = c.portal_name
and b.portal_prntobjname = c.portal_objname

To find all the content references which are hidden from Portal Navigation.

select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'C'

select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_label
from PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE' 
and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' 
and b.portal_Reftype = 'C' and a.portal_name = b.portal_name 
and a.portal_objname = b.portal_objname
and b.portal_name = c.portal_name
and b.portal_prntobjname = c.portal_objname

Replace EMPLOYEE with Your portal name. Some of the Other names are.

CUSTOMER
DEMOSITE
EMPLOYEE
MOBILE
PORTAL
PS_SITETEMPLATE
SUPPLIER

Portal Content Reference/folder Attributes are stored in

PSPRSMSYSATTR

PSPRSMSYSATTRVL

Building a Search Index and Searching the Portal - VERITY Search Engine

What does Verity Search engine do?
How to build a Search Index?
What PORTAL_INDEX application engine program does?
How to retrieve content references path by searching the portal?
This post is an attempt to answer these questions.

Verity Search Engine:
PeopleSoft Portal technology provides a tremendous search feature using the Verity search engine. PeopleSoft Portal and Verity Search technologies combine and give portal users easy and efficient search on content references registered in the portal registry.

Registry Search Index:
The portal registry collections are generated by the portal administrator and stored on the application server. This can be either done manually or by a scheduled process. The application engine used to build the registry search index is PORTAL_INDEX. 

This process can be run manually from the navigation:
PeopleTools -> Portal -> Build Registry Search Index

Verity Collection files are stored on the application server in the location:
%PS_HOME%\data\search\< index_name >\< db_name >\ < lang_cd >

• %PS_HOME% - the home directory in which PeopleSoft application server is installed
• < index_name > - name of the application or the portal name for portal registry that the collection is serving (PORTAL)
• < db_name > - as the name signifies name of the database
• < lang_cd > - PeopleSoft local language code. A single portal will have only one collection per locale. Each holds the text files used to build the collection - input.bif and input.dat

Several numbers of subdirectories exist beneath the language code directory. These hold the various elements of actual collection used by Verity to conduct the search.

Running the PORTAL_INDEX application engine process builds a search collection which includes few elements from the content references in the registry and get included in the index.

We know that Content References can be accessed from 
PeopleTools -> Portal -> Structure & Content -> <(nested) folder_name> -> ContentReference 
  
PeopleSoft Component Content References have the following information: 

• ICType
• Menu
• Market
• PanelGroupName

iScript Component Content References have the following information: 

• ICType

The following information is collected for all Content References: 

• Label
• Description
• Author
• Product
• Valid from Date
• Valid to Date
• Creation Date
• Content Provider
• URL
• Path
• Attributes

In the Content Reference Attributes, a set of keywords are entered for PeopleSoft delivered content references. These keywords are specified in Attribute Value and not in Name or Label. To add keywords to a content reference the NAME for the content reference attribute must be KEYWORD and add the search words or phrases separated by commas. 
  
To have the Label and Attribute values to be a translate table click the Translate checkbox. Two distinct tables are used for attributes. One is translatable into other languages while the other is not. For example, PORTAL_HIDE_FROM_NAV = False is used internally by the system and should not be translated however keywords should be translated. 
  
For accurate and high performance searches through the portal, the search engine must reference a comprehensive, up-to-date search index. The search index must be easy to maintain, as content is likely to change frequently within the portal registry.  
  
How the Search Index is built? 

1. The Application Engine (AE) program - PORTAL_INDEX has to be launched through the process scheduler.
2. This AE process launches a PeopleSoft C++ program. The C++ program queries the tables in the 'Portal Registry' for search content and it builds two text files. These files are created as .BIF and .DAT which are used by Verity to build its collection.
3. The AE process then launches the Verity program - MKVDK. The MKVDK program builds the Search index (Verity collection) based on the content in .BIF and .DAT files

Running the PORTAL_INDEX process:
In a busy portal where the content gets frequently changed it is absolutely important to refresh the search index often. Every time the search index is built, the existing search index is overridden. So, generally it is better to schedule this process in batch environment. Alternatively, the process can be also run manually. 
Go to: PeopleTools -> Portal -> Build Registry Search Index
After creating or reusing an existing Run Control ID, Run the process. Before running the process, under Language Options, check the 'All Installed Languages' check box as required.

Searching the Portal after building Search Index:
Type any valid keyword in Search and click 'Go'. The search performs the following steps:

1. The case of the entered text gets changed to uppercase automatically. This string enables the Verity search engine to search for the text irrespective of case type.
2. The query string is formatted and passed to the Search API. The formatting includes filtering out hidden content references, expired content references and invalid content references based upon from and to dates.
3. Calls the Search API which returns the query results
4. Calls the Portal Registry API. This is done to apply security filtering to the results. Security is applied in PeopleCode by checking the 'Authorized' property.
5. Formats and displays the search results.

For every content reference returned by the search results page, the following fields are displayed:

1. Content reference label - a hyperlink which on a click  takes directly to the content reference URL
2. Long Description of the content reference
3. Path - breadcrumbs to the content reference

Adding Custom CREF Images to Folders

Have you ever noticed that some links have a custom image in the navigation like this?

It is very easy to reference an existing image in the PeopleTools database.
In Structure and Content, navigate to the desired CREF.
Add a PTPP_IMAGE “Content Reference Attribute” to the CREF and enter the image object name in the value field.

Find Missing Portal Pagelets on a user’s home page

There are times when a user’s PeopleSoft Portal home page customization can get mixed up and pagelets go missing. For Example, if the user chose a two column layout but the tables that store the user’s homepage configuration have a pagelet set to display on the third column those pagelets will never be shown.

Here is query that will find those missing missing pagelets.

SELECT * FROM
   PSPRUHTABPGLT A,
   PSPRUHTAB B, 
   PSOPRDEFN C
WHERE A.PORTAL_NAME = B.PORTAL_NAME
AND A.OPRID = B.OPRID
AND A.PORTAL_COL_NUM > B.PORTAL_COLLAYOUT
AND A.OPRID = C.OPRID 
AND ACCTLOCK = 0

To correct this you can get the user to switch to a 3 column layout, then flip them back to a 2 column layout. This will fix the invalid entries in portal tables.

Migrate PeopleSoft Enterprise Portal Managed Content Data with DMS

Here is a DMS script that will export Enterprise Portal Managed Content data where you can import into another database.
The user interface for this is: Content Management > Managed Content > Browse Folders
I have tested this migrating data between Release 9 to Release 9.1. There did not seem to be any structure changes between those releases for these tables. I am sure someone will find this useful.

set log C:\TEMP\content_export.log;
set OUTPUT C:\TEMP\e9_cm_export.dat;

EXPORT EPPCM_CONTENT;
EXPORT EPPCM_CATG_MEM;
EXPORT EPPCM_CATG_CONT;
EXPORT EPPCM_DOC;
EXPORT EPPCM_HTMLTEXT;
EXPORT EPPCM_DOCINDB;

EXPORT EPICP_WF_CNTAPR;
EXPORT EPICP_TMPL_HDR;
EXPORT EPICP_TMPL_DETL;
EXPORT EPICP_INST_HDR;
EXPORT EPICP_INST_DETL;
EXPORT EPPCM_CATEGORY;

Step-by-Step: App Engine for Testing PeopleCode

his is a how-to post that I intend to refer back to from time to time.  The goal is to create a simple Application Engine program into which we can drop some PeopleCode and see how it works.  Assuming we don’t need any of the online pieces, this is much easier than going through all of the steps to create a page and register it so we can see it online.

Step 1: Create a new Application Engine Program

In Application Designer, click Ctrl + N or use the File > New menu option.  This will open the “New” dialog, and you can choose Application Engine program from the list.

You new program should look like this:

Step 2: Disable Restart

This step is very important.  If you don’t disable the restart and your program crashes, you will have to go through a few extra steps before you can rerun it.
First, click on the properties button while your program is in focus (you can also use the File > Definition Properties menu):

This should bring up the Properties dialog.  Then, go to the Advanced tab.  Check the “Disable Restart” option.

Step 3: Add an Action

First, click on the “Step 1″ step to select it.  I usually click anywhere in the gray, and this should turn it black.

Next, click on the Add Action button, or you can use the Insert > Action menu.

Finally, change the type from SQL to PeopleCode.

Step 4: Save the program

At this point, you need to save before you can add PeopleCode.  You can use Ctrl + S, click on the Save icon on the toolbar, or you can use the File > Save menu.

Step 5: Enter the PeopleCode

First, open the PeopleCode program by double clicking anywhere on the gray of the PeopleCode action.  Or, you can right click on it and choose the “View PeopleCode” option.
Next, you will probably want to open a file to show output from your PeopleCode testing.  You can use this PeopleCode:

Local File &log;
&log = GetFile("c:\temp\log.txt", "W", "A", %Filepath_Absolute);

Then, you can print to that file with the writeline() method.  For now, we will just print Hello, World.

&log.WriteLine("Hello, World!");

Finally, you will probably want to close your file:

&log.Close();

Here is what it all looks like:

Make sure to save once you make these changes.

Step 6: Run the Program

Again, after you have saved, go back to the main program window where you can see the Main section, Step 1, and your new PeopleCode action.  Then, click the run icon.

In the dialog, Check the Output log to file and uncheck Run Minimized.  The output log to file allows you to see what happened.  Otherwise, the window will close before you see what happened.  The run minimized isn’t a big deal, but if the program doesn’t run minimized you see it pop up and go away better.  When the program goes away, you know it is done running.

Finally, when it is done, check the output.  If you used the paths that I did your output should be in the c:\temp directory.  You should have two files.  The first, is the main output from the program.  Check this to make sure the program ran to success:

The second is the log that your PeopleCode created.  For now, it should just say, “Hello, World”.

Import/Export with Data Mover

A common development task is to move data between two different PeopleSoft environments.  You can use App Designer to move code, table structures, and pages, but data is a different story.  Data Mover is your tool for moving your data.
My biggest challenge is that I don’t use it enough to have the syntax memorized.  This post is a list of common tasks with the syntax to make it easier to remember…

Copying Data Out

The export command copies data out of the database into a DAT file.  You should set the Log parameter to save the log to a particular place.

1 2 3 4

SET LOG C:\Temp\export.log; SET OUTPUT C:\Temp\data.dat; EXPORT PS_BAS_EVT_CLASS;

You can use the record name or the table name.  So, here’s the same thing with the record name instead of the table name.

1	EXPORT BAS_EVT_CLASS;

If you want to restrict which rows from the table get written into the data file, you can add a where clause:

1 2	EXPORT BAS_EVT_CLASS WHERE EVENT_CLASS = 'CVG';

Copying Data In

The import command is the way to copy the data back into the system.  You will want to set the Log parameter and you have to set the Input parameter.

1 2 3 4

SET LOG C:\Temp\import.log; SET INPUT C:\Temp\data.dat; IMPORT BAS_EVT_CLASS;

If you want to limit which rows you want to import, you can use a Where clause.  For me, it wouldn’t work unless I used the parameter (:1).

1	IMPORT BAS_EVT_CLASS WHERE EVENT_CLASS = :1;CHAR,CVG;

Now, if the row already exists, it will ignore it.  There is a “Set Ignore_Dups” option, but I didn’t have to use it.  It just ignored the row that already existed.  To get it to update the row, I used the “Set Update_Update” option:

1 2	SET UPDATE_DUPS; IMPORT BAS_EVT_CLASS WHERE EVENT_CLASS = :1;CHAR,CVG;

These commands so far work with only the rows that are in the data file.  If the database has extra rows that you don’t want, you need to use Replace_Data.  You could truncate the table first, but the Replace_Data command is designed for just this:

1	REPLACE_DATA BAS_EVT_CLASS; Resources Oracle Forums: DATAMOVER: Error: Syntax error in where clause for PSOPRDEFN PeopleBooks: Using PeopleSoft Data Mover Commands

Improving PeopleSoft with jQuery

I must thank Jim Marion for turning me on to jQuery. He has made various posts over the last 3 or 4 years about it (see here.) I've also sat in on one or 2 presentations he's made where he's mentioned it. It's a great JavaScript library that lets you make even more improvements to your PeopleSoft application.

Since PeopleTools 8.50 (or maybe it was a late patch of tools 8.49) there is no longer a size limit on HTML Definitions. PT_PAGESCRIPT is the JavaScript definition that is included in all (or at least most) pages served up by PeopleSoft. This is now the best place for including the jQuery library.

So now that we have access to jQuery, what kind of things can we do with it? Here's the first of a couple of examples I've come up with.

Add options to the Page Bar
Have you ever tried to print a PeopleSoft page using your browsers print functionality. It doesn't always look good. Depending on the browser and what exactly has the focus when you choose Print, it may include the Portal Heading and the target content may have scroll bars included. I think it would be great if the Page Bar had an option to print the page. Here's a way of getting JavaScript and jQuery to do it for you.

Why do you need jQuery to do it? Because as a PeopleSoft developer, you don't have access to the Page Bar. It is not generated via an iScript, HTML Definition or an Application Package. You could use something like MonkeyGrease on your web servers to rewrite the response to include a new link on the Page Bar. But that seems to be a bit overkill. By using jQuery, everything is under control within Application Designer like most any PeopleSoft customisation.
So onto the code! Add the following to PT_PAGESCRIPT after the jQuery library. (I hope it is self documented well enough for you to follow through.)

//%New Print button added to Page Bar
//%Ensure that document is ready before proceeding
$(document).ready(function() {
   //%Check if the Page Bar is on the page
   if($("#PAGEBAR").length==1) {
      //% Declare local variables
      var printAnchor;
      var printImg;
      var newText = "Print Page";
 
      //%Add new style to prevent #PAGEBAR from displaying when printing
      $('<style media="print"> #PAGEBAR {display: none} </style>').appendTo('head'); 
 
      //%Get the Page Bar div and table cell
      var pgBar = $("#PAGEBAR");
      var pgBarLinksCell = $("table > tbody > tr:eq(0) > td:eq(2)", pgBar);
 
      //%Check if there are any anchors on the Page Bar 
      //%Though it seems if there is a Page Bar then there is always an anchor 
      if ( $("a", pgBarLinksCell).length > 0 ) {
        //% If it is, clone the last one and separate the image from the anchor
        printAnchor = $("a:last", pgBarLinksCell).clone();
        printImg = $("img", printAnchor);
        printAnchor.html("");
      } else {
        //%otherwise build it manually 
        printAnchor = $("<a tabindex='0'></a>");
        printImg = $("<img hspace='0' border='0' align='absmiddle' vspace='0' ></img>");
        var cssObj = {
           'font-size'       : '9pt', 
           'font-weight'     : 'normal', 
           'font-style'      : 'normal',
           'color'           : 'rgb(51, 102, 153)', 
           'text-decoration' : 'none'
        }
        printAnchor.css(cssObj);
      }
      //%set (or overwrite) the anchor attributes
      printAnchor.attr("id", "OXFPRINT");
      printAnchor.attr("name", "OXFPRINT");
      printAnchor.attr("href", "");
      printAnchor.attr("tabindex", parseInt(printAnchor.attr("tabindex"))+1);
 
      //%set (or overwrite) the image attributes
      printImg.attr("title", newText);
      printImg.attr("alt", newText);
      printImg.attr("src","/cs/FSQA850/cache/PT_PRINT_1.gif");
 
      //%Add a click event to the anchor
      printAnchor.click(function(event) {
 
        //%prevent the default from occuring
        event.preventDefault();
 
        //%IE work-around printing in an iframe
        try { 
          document.execCommand('print', false, null); 
        } 
        catch(e) { 
          window.print(); 
        }
        return false;
      });
 
      //%Combine (or recombine) anchor, image and text
      printAnchor.wrapInner(printImg).append("&nbsp;"+newText);
 
      //%append the new Print Anchor to the end of the Page Bar
      $(pgBarLinksCell).append("&nbsp;&nbsp;&nbsp;").append(printAnchor);
   }
   else {
      //%write the code to include a Page Bar
   }
});

Here's what it looks like

Points to Consider

• The path to the Printer image is hard coded. Meta-HTML %Image won't work in this situation because it is a JavaScript library. To make it better, you could use %URL or create an iScript that uses %Request.GetImageURL to return the path to the image and use jQuery within the script to make an AJAX call to the iScript.
• Also, I think the image is too gray. It's based on the older style. To match the newer swan style it should have more blue.
• I've tested this on IE7, FireFox 3.6 and Google Chrome 7.0.5. It's possible it won't work with other browsers.

Summary

With jQuery you have an extra layer of control over your user's interface. This was just one example of what can be done using the jQuery JavaScript library. I hope to post another couple examples soon.

PeopleSoft Branding

Here's one way to change your PeopleSoft Portal Header from this

to something like this

Preface
I was grateful I discovered @peoplesoftwiki's great post about PeopleSoft Branding. It meant that this post wouldn't have to be quite as long. Because as it turned out I followed the same steps as @peoplesoftwiki (extending Application Package PT_BRANDING and overriding the Branding Package on PeopleTools Options page.) I went one or 2 steps further it seems so I'd like to expand on that.

Before I begin, I need to tell you the main assumption I made. We are moving to PeopleTools 8.50 and we have decided to use the new Swan style. This is important as there is code in PT_BRANDING methods that is wrapped in if statements checking whether the style = "swan". That meant I only had to focus on making changes to items where this was true.

And one more thing before going any further: configuration good, customisation bad. (Though I don't always follow that mantra.)

Application Package
So just like @peoplesoftwiki, I extended PT_BRANDING. I made a few changes, including:

1. adding new methods to get data out of the system that I wanted to display in the Portal Header
• setGreeting, to prefix the Users greeting as set in Personalize Content with the description of the Environment as set in PeopleTools Options
• getOxfamLinks, to read and labels and links from the message catalog (labels being the message, and the links being the explanation text) to be displayed along the top of our branded portal.
2. replacing calls to the delivered HTML Definitions that formed the basis of the Portal Header. Those 3 methods and the HTML defintions are
• getIframeHeaderHTML - PT_IFRAME_HDR_SWAN
• GetExpPasswordHdrHTML - PORTAL_EXP_PASSWORD_HDR
• GetUniHeaderHTML - PORTAL_UNI_HEADER_NNS_SWAN

The changes made to the methods getIframeHeaderHTML, GetExpPasswordHdrHTML and GetPortalUniHeaderNNS were all very similar, so I'll just go in to details for the method getIframeHeaderHTML. I basically left the code all the same, except for adding calls to my new methods setGreeting and getOxfamLinks and another call to get some more text from the message catalog and a call to the GetPortalHomepageURL() function to retrieve the Homepage URL.

HTML Definition
To make use of all this, I would either need to change the delievered HTML definition or create my own. So I created my own by opening the delivered PT_IFRAME_HDR_SWAN and doing a Save As to create OXF_IFRAME_HDR_SWAN. By doing this I had complete control over the HTML I wanted displayed. The main change I made was to the pthdr2container <div>, changing it from

<div id="pthdr2container">
<div id="pthdr2logoswan"> </div>
<div id="pthdr2greeting">
<span class="greeting">%bind(:15)</span>
</div>
%bind(:16)
<ul id="pthdr2links">
%bind(:17)
</ul>
</div>

to

<div id="pthdr2container">
<div class="oxfblend">
<div id="pthdr2logoswan"
 onclick="javascript:window.location='%bind(:27)';return false;">
</div>
<div id="pthdr2greeting">
<span class="oxfps">%bind(:26)</span>
<span class="oxfgreeting">%bind(:15)</span>
</div>
<ul id="oxfhdrlinks">
%bind(:25)
</ul>
<ul id="pthdr2links">
<span>%bind(:17) </span>
</ul>
</div>
<div id="oxfheaderLine3">
<div class="clearer"></div><!--Important - ensures floats
are contained-->
</div>
</div>

The main changes were adding new <div> oxfblend, oxfheaderLine3 and clearer, new <span> oxfps and oxfgreeting and new <ul> oxfhdrlinks. I also removed %bind(:16), the search box, which we didn't think made any sense to have in the Portal Header.

Stylesheets
To make all these new elements on the page look any good, I had to update the Stylesheet. PSHDR2_SWAN held all the styles to control the Portal Header. For example, it defined the style pthdr2logoswan which has the background image as the Oracle Logo. Well we wanted to use the Oxfam Logo so I made a copy of PSHDR2_SWAN to create OXF_HDR2_SWAN and I made my changes to that.

Remember, configuration good, customisation bad. So I copied PSSTYLEDEF_SWAN to create OXF_STYLEDEF_SWAN. And in OXF_STYLEDEF_SWAN, I replaced PSHDR2_SWAN with OXF_HDR2_SWAN. When you switch to the new swan style, you update Default Stylesheet on PeopleTools Options to PSSTYLEDEF_SWAN. I instead switched it to OXF_STYLEDEF_SWAN.

Images
I had to create 4 image definitions in App Designer. Each of these are referenced in the stylesheet OXF_HDR2_SWAN as background images for 4 different elements.


Bringing it all Together
So here's how it all works together. The PT_BRANDING extended Application Package (with new methods) calls the new HTML Definitions. Updated or newly created bind parameters are passed to the custom HTML Definition to generate the content of the Portal Header. The new stylesheets and images provide the formatting for the Portal Header HTML. What you can come up with is only limited by your imagination.

Link To Portal Folder

The code below can be used for a link to navigate to your portal folder.

Declare Function NavPageURL PeopleCode EOPP_SCRTN_WRK.FUNCLIB FieldFormula;

&LINKURL = NavPageURL(%Portal, %Node, "your-folder-object-name", "PSC", "", "", "False", "", "", "");
%Response.RedirectURL(&LINKURL);

Pulling Page Labels in Query

Here is the table that table contains the online labels for the pages.

The record that stores this information is PSPNLGROUP. So if you run the following query:

select * 
from PSPNLGROUP 
where PNLGRPNAME = 'JOB_DATA' 

You will see the labels in the ITEMLABEL column. This is what you see when you open the JOB_DATA component in application designer.
One thing that you may not want to see in your queries is the & character used for keyboard shortcuts. To strip these out you can use the REPLACE command in Oracle like so:

select REPLACE(ITEMLABEL, '&', '') as ITEMLABEL 
from PSPNLGROUP 
where PNLGRPNAME = 'JOB_DATA'; 

You will need to create the line REPLACE(ITEMLABEL, '&', '') as ITEMLABEL as an expression in PeopleSoft Query and use it as a field.
By the way pages used to be called panels and components used to be called panel groups which is why the PeopleTools record and field names aren't what you would expect.