PeopleSoft

PeopleSoft related articles.

Is Component Attached to a Menu?

This SQL will help you find the menus a specific component is attached to.

-- Is component attached to a menu?
-- List all the Menus that have a specific component
SELECT * FROM PSMENUITEM menu
WHERE pnlgrpname = :component_name
AND ITEMTYPE <> 8;

Display Pages Associated to a Component

For example, if you have a component that has 3 pages on it, then when you run this SQL, lets say in your test environment, you should have 3 rows back.

SELECT page.* FROM PSPNLDEFN PAGE, PSPNLGROUP COMP
WHERE COMP.PNLGRPNAME = :component_name
AND PAGE.PNLNAME = COMP.PNLNAME;

PeopleSoft SQL Snippets

PeopleSoft specific SQL snippets. Fire away!

Check What Record Has Your Fields

This is another quick SQL snippet that I find useful. When would you use it?
Well, you forgot a certain record name, but you know that the record has EMPLID, NAME, EMPL_REC fields on it.
You can query the database to find out what records have the fields you are looking for.

SELECT RECNAME, COUNT(FIELDNAME)
FROM PSRECFIELDALL
WHERE FIELDNAME IN ('EMPLID', 'NAME', 'EMPL_RCD')
GROUP BY RECNAME
HAVING COUNT(FIELDNAME) = 3;

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,

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;

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.

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