Lepa's blog

Get all Components Under a Certain Portal Folder

Use this SQL snippet to return all components plus sub-folders (if any) under a specific portal folder.

For example, lets say you have a folder called "Workforce Admin". Under Job Information, you have a sub-folder called "Job Information" and few components.

Run the SQL against the Workforce Admin folder and you should get a row for the sub-folder, and a row for each of the components.

-- Get all sub-folders and components under a certain folder name --
SELECT PORTAL_NAME,
   PORTAL_PRNTOBJNAME AS FOLDER,
   PORTAL_OBJNAME AS CONTENT_REFRENCE,

Visual Basic Macro to Delete Blank Rows

Users love "copy & past" from one excel document to another. This however can result in having some empty rows in the document and could cause some major headache if you are trying to generate a .txt file and upload it for processing.

One way around this is to create an excel macro that will delete blank rows once the user generates the .txt file.

Here is the code snippet:

'Deletes blank rows before creating the TAB delimited file
   Dim R As Long
    Dim C As Range
    Dim Rng As Range

    On Error GoTo EndMacro
    Application.ScreenUpdating = False

PeopleSoft PeopleTools Meta-Tables

THE CONTENT OF THIS PAGE HAS BEEN MOVED TO A NEW WIKI STYLE PAGE.

Component, Content Reference, and Menu

Ok, so we all know that a component needs to be attached to a menu before you are able to register it to the portal structure. Once a component is registered using Application Designer, then a content reference is created and your component will be available under the folder and path you specified during the registration process.

Absolute Value and Date Functions

Here is a quick PeopleCode snippet to check the difference between two dates.

&date = Abs(Days365(&yourDate, %Date));

Use the Abs PeopleCode function to return a decimal value equal to the absolute value. This is very useful if you are validating that a date must be within X days before or X days after sysdate.
/* notice since we are using the Abs function, we don't need to code for &date < 90 */
IF &date > 90 Then
   /*your error message*/
End-IF;

Oracle Group By ROLLUP

It would be safe to assume that most if not all of us are familiar with Oracle "Group By" clause. You can however use the ROLLUP function to enhance the grouping actions performed within your queries.

Lets take a look at a fairly complex Group By example and then re-write it using the Group By ROLLUP.

Move or Hide Portal Search Box

Part of our upgrade requirement is to hide the search box on the navigation menu as we are not using it. It must be easy to do, right? Well yes and no. Yes, if you know where to go, and No, if you don't. It will just take some time to dig around to find the right place to turn it off (Hide it).

To hide the search box navigate to:
Root >> PeopleTools >> Portal >> General Settings. Locate the second box on the page and there you should see a check box that says "Display search in navigation". Uncheck it, clear cache and sign back in. Voila, search box is gone.

Search Strings REGEXP_SUBSTR

As of Oracle 10g, the SUBSTR has been enhanced to support searches for regular expressions. The new function is named REGEXP_SUBSTR. You can use the new function to perform advanced searches against a string.

Syntax:

REGEXP_SUBSTR(source_string, pattern
                            [,position
                               [, occurrence
                                   [, match_parameter]
                               ]
                             ]
                            )
Where:
source_string: is the text to search within

How to Add Items to a Drop Down List?

The PeopleCode below will do couple things. First, it will restrict grid on a page to show only rows of address type "HOME". Second, allow users to only be able to add a "Home" mailing address by restricting the values in the drop down list to show only "Home" by using the ClearDropDownList and AddDropDownItem properties.

/* Hide Rows on the grid for any thing that is not of Address Type "HOME" */
FOR &i = 1 TO &Level1.ActiveRowCount
   &AddrType = FetchValue(Scroll.PERSON_ADDRESS, &i, PERSON_ADDRESS.ADDRESS_TYPE);
   IF &AddrType <> "HOME" Then

PeopleSoft Object Types Explained

If you ever queried PSPROJECTITEM table (stores PeopleTools project objects) then you have definitely noticed that object types are stored as a number and not with their names. For example, if you have a project with a component then your component on this table will be stored as object type number 7.

So, what are other object types stored as (Pages, Record PeopleCode, Menus....)? Well, the list below is your answer. Please don't hesitate to post a comment and "correct" me if I messed up somewhere while mapping numbers to names!

0 = 'Record'
1 = 'Index'
2 = 'Field'