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.