Category: PeopleTools Tip

Adding the PeopleTools Breadcrumbs Back

In these recent PUMs, one of the things that I miss is the breadcrumbs navigation at the top. It has many advantages over the new navigation. For example:

  • Taking a screenshot documents the navigation to the page
  • Navigation is quicker for nearby pages
  • Reloading the page is quicker by clicking the link in the navigation rather than reloading the whole browser page

I found the breadcrumbs are easy to turn back on. Simply, navigate to PeopleTools > Portal > Branding > Define Headers and Footers. In the search, open up “DEFAULT_HEADER_FLUID”.

At the bottom of the page, in the “Style Definitions” field, paste the following CSS:

.desktopFluidHdr .ptdropdownmenu {
    display: block;
}

Finally, save the page with the button near the top. After you logout and back in, you will see the breadcrumbs.

Resources

Searching with USEEDIT

Because many of the record properties are hidden in the Bit Map field USEEDIT, it is hard to search for properties.  If you are on Oracle, here’s some SQL that might help…

This SQL lists all of the fields with their properties broken out of that field:

SELECT FIELDNAME,
       USEEDIT,
       Bitand(USEEDIT, 1)         KEY,
       Bitand(USEEDIT, 2)         DUP_KEY,
       Bitand(USEEDIT, 4)         SYS_MAINT,
       Bitand(USEEDIT, 8)         AUDIT_ADD,
       Bitand(USEEDIT, 16)        ALT_SEARCH_KEY,
       Bitand(USEEDIT, 32)        LIST_BOX_ITEM,
       Bitand(USEEDIT, 64)        ASCENDING_KEY,
       Bitand(USEEDIT, 128)       AUDIT_CHANGE,
       Bitand(USEEDIT, 256)       REQUIRED,
       Bitand(USEEDIT, 512)       XLAT,
       Bitand(USEEDIT, 1024)      AUDIT_DEL,
       Bitand(USEEDIT, 2048)      SEARCH_KEY,
       Bitand(USEEDIT, 4096)      EDIT_REASONABLE_DATE,
       Bitand(USEEDIT, 8192)      EDIT_YES_NO,
       Bitand(USEEDIT, 16384)     EDIT_PROMPT_TABLE,
       Bitand(USEEDIT, 32768)     AUTO_UPDATE,
       Bitand(USEEDIT, 65536)     BIT65536,
       Bitand(USEEDIT, 131072)    BIT131072,
       Bitand(USEEDIT, 262144)    FROM_SEARCH,
       Bitand(USEEDIT, 524288)    TO_SEARCH,
       Bitand(USEEDIT, 1048576)   EDIT_BINARY,
       Bitand(USEEDIT, 2097152)   DISABLE_ADV_SEARCH,
       Bitand(USEEDIT, 4194304)   REGULAR_FIELD,
       Bitand(USEEDIT, 8388608)   DEFAULT_SEARCH_FIELD,
       Bitand(USEEDIT, 16777216)  BIT16777216,
       Bitand(USEEDIT, 33554432)  SEARCH_EDIT_KEYS,
       Bitand(USEEDIT, 67108864)  BIT67108864,
       Bitand(USEEDIT, 134217728) BIT134217728,
       Bitand(USEEDIT, 268435456) BIT268435456
FROM   PSRECFIELDALL
WHERE  RECNAME = 'JOB'; 

Read More

Search for a Field with Translates

I was looking for an “Internal/External” field.  I wanted a field already built that had two translate values: “E” and “I”.  This SQL did the trick:

SELECT A.FIELDNAME,
A.XLATLONGNAME,
B.XLATLONGNAME,
A.XLATSHORTNAME,
B.XLATSHORTNAME
FROM   PSXLATITEM A,
PSXLATITEM B
WHERE  A.FIELDNAME = B.FIELDNAME
AND A.FIELDVALUE = 'E'
AND B.FIELDVALUE = 'I'
AND Upper(A.XLATLONGNAME) LIKE 'EXT%'
AND NOT EXISTS (SELECT 'X'
FROM   PSXLATITEM C
WHERE  A.FIELDNAME = C.FIELDNAME
AND C.FIELDVALUE NOT IN ( 'E', 'I' )) 

I found the INTERNAL_EXTERNAL field, which works just perfect for me.

Deleting Projects from PeopleSoft

Have you ever needed to delete a project with all of its objects from an environment?  I don’t know if this necessarily something you would do in a real production environment, but maybe some of the thoughts will help someone out.

I wanted to get all of my little development projects out of this environment to return it back as close to a clean demo environment as possible.  Here’s what I did:

Step 1: Security

If your project contains any Roles or Permission Lists, you need to remove those from the User Profiles first.
A Role in a Project

I learned this the hard way. My user was connected to one of the roles that I deleted via the project. All of the sudden, I couldn’t get to anything inside PeopleSoft Online. I couldn’t log back into App Designer anymore. I had to remove the role from PSROLEUSER manually in the database before I could do anything.

You can use this query to check any roles:

SELECT ROLEUSER, ROLENAME
FROM PSROLEUSER
WHERE ROLENAME = '<role name>';

If it is a permission list, you may want to check this query:

SELECT R.ROLEUSER, R.ROLENAME, C.CLASSID
FROM PSROLEUSER R, PSROLECLASS C
WHERE C.CLASSID = '<permission list name>'
AND R.ROLENAME = C.ROLENAME;

Here’s where I am deleting the Role from my user:
Deleting the Role

Step 2: Check your project

Make sure you know what you are deleting! You can’t get these objects back unless you have a backup.

Click on the Upgrade tab and just go through all the folders to make sure you know what objects are included. You want to watch for any delivered objects that you had modified that you don’t really want deleted.
Clicking on the Upgrade Tab in the Project

Step 3: Change the Upgrade Action

Now, you need to change the upgrade action for each of the objects. If this is a large project, you will want to do this from the database:

UPDATE PSPROJECTITEM
SET UPGRADEACTION = 1
WHERE PROJECTNAME = '<project name>';

You can see it from the Upgrade tab in the project:
Upgrade Action set to Delete

Step 3: Export the Project to file

Use the menu Tools > Copy Project > To File… Make sure all the object types are selected, and key in the path to a temporary folder.
The Copy to File Dialog

Step 4: Import the Project back from File

Use the menu Tools > Copy Project > From File … Browse to the same path where you save the project. Select that project and import it:
Selecting the Project

Make sure that you select to use the project definition from the File:
Selecting to use the project from the file.

Again, select all the object types and hit copy:
Copy Project From File dialog

Step 4: Confirm Objects Deleted

Now, try to open a few of the objects from the project. You should get a message that it doesn’t exist:
Object Does Not Exists Message

Step 5: Clean up the Database

Here is some SQL that should generate the Drop statements for all of the tables involved in the project. This is the Oracle version:

SELECT 'DROP TABLE ' || TABLE_NAME || ';'
FROM DBA_TABLES A, PSPROJECTITEM B
WHERE B.PROJECTNAME = '<project name>'
AND B.OBJECTTYPE = 0
AND 'PS_' || B.OBJECTVALUE1 = A.TABLE_NAME;

SELECT 'DROP VIEW ' || VIEW_NAME || ';'
FROM DBA_VIEWS A, PSPROJECTITEM B
WHERE B.PROJECTNAME = '<project name>'
AND B.OBJECTTYPE = 0
AND 'PS_' || B.OBJECTVALUE1 = A.VIEW_NAME;

This is the SQL Server version:

SELECT 'DROP TABLE ' + TABLE_NAME + ';'
FROM DBA_TABLES A, PSPROJECTITEM B
WHERE B.PROJECTNAME = '<project name>'
AND B.OBJECTTYPE = 0
AND 'PS_' + B.OBJECTVALUE1 = A.TABLE_NAME;

SELECT 'DROP VIEW ' + VIEW_NAME + ';'
FROM DBA_VIEWS A, PSPROJECTITEM B
WHERE B.PROJECTNAME = '<project name>'
AND B.OBJECTTYPE = 0
AND 'PS_' + B.OBJECTVALUE1 = A.VIEW_NAME;

Step 6: Delete the Project

Now, you are done with your list of definitions, and you can just remove the whole project using App Designer. Use the File > Delete … option.
Deleting the Project

Step 7: Check your system.

Run the DDDAudit and the SysAudit and make sure that you haven’t left anything broken. You can run those from PeopleTools > Process Scheduler > System Process Requests.

Tip: Comparing Trace Files

Trace files can give a lot of nice troubleshooting information, sometimes too much.  Sometimes, you may have a situation where a problem occurs in one system but not in another.  You could run a trace in both systems, but comparing those trace files is difficult.  Each line has a timestamp, which will be different in each file.

Here is a trick for getting rid of those timestamps.  Here is the source:

sed : Remove first 4 letters in each line

Of course, you need Linux, Unix, or Cygwin for this — something with the sed program.  Here is the command:

sed 's/^.\{51\}//g' "mytrace.tracesql" > modtrace.tracesql

For example, here are the first few lines of a trace file:

PSAPPSRV.248 (181) 	 1-1      10.24.26             Cur#1.248.FSDEV90 RC=0 Dur=0.001000 COM Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'
PSAPPSRV.248 (181) 	 1-2      10.24.26    0.003000 Cur#1.248.FSDEV90 RC=0 Dur=0.001000 COM Stmt=SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME FROM PSPRSMDEFN WHERE VERSION > :1 UNION SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME FROM PSPRSMDEL WHERE VERSION > :2
PSAPPSRV.248 (181) 	 1-3      10.24.26    0.000000 Cur#1.248.FSDEV90 RC=0 Dur=0.000000 Bind-1 type=8 length=4 value=214748355

After running the command, here is what it looks like:

Cur#1.248.FSDEV90 RC=0 Dur=0.001000 COM Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'
Cur#1.248.FSDEV90 RC=0 Dur=0.001000 COM Stmt=SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME FROM PSPRSMDEFN WHERE VERSION > :1 UNION SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME FROM PSPRSMDEL WHERE VERSION > :2
Cur#1.248.FSDEV90 RC=0 Dur=0.000000 Bind-1 type=8 length=4 value=214748355

Now, you can use a tool like Beyond Compare or Meld to compare the trace files without the timestamp.  If you don’t have a compare tool, look here.

skp@pecan: ~-Downloads_857

Good Looking Messages

I have often wanted to display an informational message at the top of the page.  Rather than just slap a Long Edit Box at the top of the screen, I decided to look for a nice looking way to do it.  One of the great things about PeopleSoft, it always has an example somewhere in the huge product for what you are trying to do.

Here are some of the messages that I found:

Information Message:

Example Page: SSS_STDNTCTR_SR_SP

Here are the different pieces:

  • Stylesheet of the page: SSS_STYLESHEET
  • Group Box:
    • Label Stylesheet: PAGROUPBOXLABELINVISIBLE
    • Body Stylesheet: SSSMSGINFOFRAME
  • Static Image:
    • Image: PS_CS_MESSAGE_INFO_ICN
    • Size: 23×23
  • Static Text:
    • Style: SSSMSGINFOTEXT

Alert Message:

Example Page: SSF_SS_ERRORMSG

Here are the different pieces:

  • Page Stylesheet: SSS_STYLESHEET
  • Group Box
    • Label Stylesheet: PAGROUPBOXLABELINVISIBLE
    • Body Stylesheet: SSSMSGALERTFRAME
  • Push Button (for the image, you could use a static icon instead)
    • Image: PS_CS_MESSAGE_ALERT_ICN
    • Size: Large Icon
  • Long Edit Box (works the same as the static textbox, but lets you change it from code)
    • Stylesheet: SSSMSGALERTTEXT
    • No Label

PeopleTools Tip: PeopleCode Dump

Application Designer has an option to search through the text of the code, but it takes way too long to search the whole system on a regular basis. Thankfully, Application Designer also has a way to dump all of the code to a text file.

From Application Designer, choose Edit > Find In…

Next, enter a semicolon(;) for the search text and check the option “Save PeopleCode to File”

Important: This will take a long time and a lot of memory to finish. So, increase your virtual memory and let it run overnight. If you have a team of developers, have one person do the dump and put it on a network location where everyone can view it.

If you don’t enough memory, you may get an error message like this:

If so, go to the control panel and open the System icon.  Go to the Advanced tab and click the Settings button under performance.  Under the “Advanced” tab in the performance options dialog, change the virtual memory.  The steps to increase the memory might be slightly different depending on your flavor of Windows.

PeopleTools Tip: Finding a Component Interfaces

If you know the name of the component interface, you can easily find it in App Designer.  Just press Ctrl+O or File > Open and search for it by name.  But, what if you only know the name of the component that the component interface accesses?

You have to go to the database.  Try this SQL (replacing <Component_Name> with your actual component) :

SELECT * FROM PSBCDEFN
WHERE BCPGNAME = '<Component_Name>'

PeopleTools Tip — Cloning a Component Interface

Have you ever tried to recreate a component interface and allow PeopleSoft to default the properties and collections? The hard part is that you have to make sure that it uses the same names as your old one or you will have to change your code.

The following SQL helped me figure how to change my code to refer to the correct fields (ex. effdt or effdt0). I was cloning the delivered CI_JOB_DATA component interface with a brand new customized component interface.


SELECT A.BCNAME, A.BCTYPE || ‘:’ || A.BCSCROLL || ‘:’ || A.BCSCROLLNUM || ‘->’ || A.BCSCROLLNAME LOC, A.BCITEMPARENT, A.BCITEMNAME,
A.RECNAME, A.FIELDNAME,
B.BCNAME, B.BCTYPE || ‘:’ || B.BCSCROLL || ‘:’ || B.BCSCROLLNUM || ‘->’ || B.BCSCROLLNAME LOC, B.BCITEMPARENT, B.BCITEMNAME
FROM PSBCITEM A, PSBCITEM B, PSBCITEM AP, PSBCITEM BP
WHERE A.BCNAME = ‘<Old component interface name>
AND A.BCNAME = AP.BCNAME(+)
AND B.BCNAME = ‘<New component interface name>
AND B.BCNAME = BP.BCNAME(+)
AND A.BCITEMPARENT = AP.BCITEMNAME(+)
AND B.BCITEMPARENT = BP.BCITEMNAME(+)
AND A.RECNAME = B.RECNAME
AND A.FIELDNAME = B.FIELDNAME
AND (BP.RECNAME = AP.RECNAME OR (BP.RECNAME IS NULL AND AP.RECNAME IS NULL))
AND A.BCITEMPARENT = ‘<Parent collection name>
AND A.BCITEMNAME = ‘<Item name>

Examples:
<Old component interface name> — CI_JOB_DATA
<Parent collection name> — COLL_JOB_EARNS_DIST
<Item name> — KEYPROP_ERNCD

PeopleTools Tip: Searching for Records

You can find records that contain a specific field with the find definition references tool in App Designer, but what if you want to know what record contains field1 and field2?

You can go to the database to file that answer:

SELECT A.RECNAME
FROM PSRECFIELDALL A, PSRECFIELDALL B
WHERE A.RECNAME = B.RECNAME
AND A.FIELDNAME = ‘FIELD1’
AND B.FIELDNAME = ‘FIELD2’