"Find Definition References" for Page and Field Configurator

A student recently asked:

Is there an Edit | Find Definition References equivalent for Page and Field Configurator?

Great question! In Application Designer, we can open a field and choose Edit | Find Definition References to find all usages of that field. Unfortunately, Page and Field Configurator does not have an equivalent. The good news, however, is Page and Field Configurator is metadata driven. In other words, we can create our own "Find Definition References" equivalent by writing SQL. Here is a short example to get you started:

SELECT *
  FROM PS_EOCC_CONFIG_FLD
 WHERE FIELDNAME = 'DESCR'

The EOCC_CONFIG_FLD record contains the Component name, Record name, and Field name, allowing us to effectively "Find Definition References" for any of those three items.

At JSMpros, we teach PeopleTools Tips like this every week. Be sure to check our website to see what we are offering next!

Which Query Security Tree?

Here is the scenario:

A user needs to create a query, but doesn't have security access to view a record. The user asks you for help.

Before running or writing queries, a user must have access to records through Query Security Trees. We assign records to trees, trees to permission lists, permission lists to roles, and roles to users. The solution is trivial: grant security access through a security tree. The difficult part is deciding which tree and which permission list. When I receive a request like this, I have two groups of questions:

1. Is there already a query security tree that grants access to the target record? If so, which one and which permission lists? Would any of those permission lists be appropriate for this user?
2. Is there already a query security tree available to this user that would be a good fit for this record?

Here are some SQL statements I created to help answer these questions.

Find existing trees that include the target record:

Finding Permission Lists that grant access to a specific record is more challenging. Locating permission lists that contain a tree that contains a record is trivial. Narrowing that list based on Access Group is more challenging because Access Groups represent node hierarchies and therefore require recursive logic:

Do you have SQL statements and tricks to help you manage Query Security? If so, share them in the comments!

At JSMpros, we teach PeopleTools tips like this every week. Check out our website to see what we are offering next!

Identifying Conditional Navigation Content References

As PeopleSoft customers upgrade to Fluid-enabled applications, it is quite common to start with Fluid disabled, and then implement Fluid behavior post go-live. It is all about change management and an organization's ability to digest change. Even though Oracle has set retirement dates for certain Classic components, with the first wave retiring December 31, 2017, all Classic functionality is still supposed to be present and available. But if you have opened a recent PeopleSoft image or are in the middle of updating to a recent HCM build, you may be asking, "Where are those delivered and supported Classic components?" If you investigate the portal registry, you will see they exist, they just don't appear in any menus. Many of these menu items, such as the Personal Details menu items, use a feature named Conditional Navigation. PeopleSoft uses Conditional Navigation to conditionally replace Classic menu items with their Fluid counterparts. You can read more about Conditional Navigation in PeopleBooks at PeopleTools PeopleBooks entry Products > Development Tools > Portal Technology > Understanding Conditional Navigation. A question I hear regularly is, "How do I temporarily enable Classic components in my PeopleTools 8.55+ environment?" The first step is to disable Conditional Navigation and the MyOracle Support document 2215964.1 describes how. Armed with this information, you now know what it is, how to configure it, and how to disable it, but how do you find it? I mean, what components are preconfigured for conditional navigation? Since conditional navigation uses CREF attributes, CREF identification requires a small SQL statement:

That is nice, but the portal registry isn't structured that way. Next question: How do I find those CREFs in the portal registry? Here is the longer form SQL statement. Results contain complete paths to conditionally configured items. Those of you that follow my blog may recognize this SQL as a derivative of Query for Component and/or CREF Navigation Take II

Query for Component and/or CREF Navigation Take II

Several years ago I wrote the post Query for Component and/or CREF Navigation which demonstrated how to use Oracle's connect by clause with the portal registry to find the navigation to a PeopleSoft component. Why? Most users are trained to send a Ctrl-J screenshot to their developers when they encounter issues. Knowing the menu, market, and component is great, but developers need to know the navigation in order to replicate the issue. The point of the query is to find the navigation to some component or CREF without having to ask a functional expert for more details. As of 11gR2, Oracle now supports recursive common table expressions, a T-SQL feature supported by Microsoft and DB2. Here is a new iteration of that old post that uses Common Table Expressions. For those using non-Oracle databases, be sure to update the concatenation character to match your database platform. If you use this SQL within PeopleSoft (as a view, etc), then save yourself some potential grief and use the %Concat Meta-SQL variable instead of a database specific concatenation operator.

Full portal registry path

WITH PORTAL_REGISTRY (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_URI_SEG1, PORTAL_URI_SEG2, PORTAL_URI_SEG3, PATH) AS (
SELECT P.PORTAL_NAME
     , P.PORTAL_REFTYPE
     , P.PORTAL_OBJNAME
     , P.PORTAL_LABEL
     , PORTAL_URI_SEG1
     , PORTAL_URI_SEG2
     , PORTAL_URI_SEG3
     , P.PORTAL_LABEL AS PATH
  FROM PSPRSMDEFN P
 WHERE P.PORTAL_PRNTOBJNAME = ' '
UNION ALL
SELECT P_ONE.PORTAL_NAME
     , P_ONE.PORTAL_REFTYPE
     , P_ONE.PORTAL_OBJNAME
     , P_ONE.PORTAL_LABEL
     , P_ONE.PORTAL_URI_SEG1
     , P_ONE.PORTAL_URI_SEG2
     , P_ONE.PORTAL_URI_SEG3
     , PATH || ' --> ' || P_ONE.PORTAL_LABEL AS PATH
  FROM PORTAL_REGISTRY P
 INNER JOIN PSPRSMDEFN P_ONE
    ON P.PORTAL_NAME = P_ONE.PORTAL_NAME
   AND P.PORTAL_REFTYPE = 'F'
   AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME
 WHERE P_ONE.PORTAL_PRNTOBJNAME != ' ' )
   
SELECT PORTAL_NAME
     , PORTAL_OBJNAME
     , PORTAL_REFTYPE
     , PATH
     , PORTAL_LABEL
  FROM PORTAL_REGISTRY
 WHERE PORTAL_REFTYPE != 'F'

Find the path when you know the CREF ID:

WITH PORTAL_REGISTRY (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_URI_SEG1, PORTAL_URI_SEG2, PORTAL_URI_SEG3, PATH) AS (
SELECT P.PORTAL_NAME
     , P.PORTAL_REFTYPE
     , P.PORTAL_OBJNAME
     , P.PORTAL_LABEL
     , PORTAL_URI_SEG1
     , PORTAL_URI_SEG2
     , PORTAL_URI_SEG3
     , P.PORTAL_LABEL AS PATH
  FROM PSPRSMDEFN P
 WHERE P.PORTAL_PRNTOBJNAME = ' '
UNION ALL
SELECT P_ONE.PORTAL_NAME
     , P_ONE.PORTAL_REFTYPE
     , P_ONE.PORTAL_OBJNAME
     , P_ONE.PORTAL_LABEL
     , P_ONE.PORTAL_URI_SEG1
     , P_ONE.PORTAL_URI_SEG2
     , P_ONE.PORTAL_URI_SEG3
     , PATH || ' --> ' || P_ONE.PORTAL_LABEL AS PATH
  FROM PORTAL_REGISTRY P
 INNER JOIN PSPRSMDEFN P_ONE
    ON P.PORTAL_NAME = P_ONE.PORTAL_NAME
   AND P.PORTAL_REFTYPE = 'F'
   AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME
 WHERE P_ONE.PORTAL_PRNTOBJNAME != ' ' )
   
SELECT PORTAL_NAME
     , PORTAL_OBJNAME
     , PORTAL_REFTYPE
     , PATH
     , PORTAL_LABEL
  FROM PORTAL_REGISTRY
 WHERE PORTAL_REFTYPE != 'F'
   AND PORTAL_NAME = 'EMPLOYEE'
   AND PORTAL_OBJNAME = 'PT_EMAIL_PSWD_GBL'

Find the path when you know the menu, component, and market

WITH PORTAL_REGISTRY (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_URI_SEG1, PORTAL_URI_SEG2, PORTAL_URI_SEG3, PATH) AS (
SELECT P.PORTAL_NAME
     , P.PORTAL_REFTYPE
     , P.PORTAL_OBJNAME
     , P.PORTAL_LABEL
     , PORTAL_URI_SEG1
     , PORTAL_URI_SEG2
     , PORTAL_URI_SEG3
     , P.PORTAL_LABEL AS PATH
  FROM PSPRSMDEFN P
 WHERE P.PORTAL_PRNTOBJNAME = ' '
UNION ALL
SELECT P_ONE.PORTAL_NAME
     , P_ONE.PORTAL_REFTYPE
     , P_ONE.PORTAL_OBJNAME
     , P_ONE.PORTAL_LABEL
     , P_ONE.PORTAL_URI_SEG1
     , P_ONE.PORTAL_URI_SEG2
     , P_ONE.PORTAL_URI_SEG3
     , PATH || ' --> ' || P_ONE.PORTAL_LABEL AS PATH
  FROM PORTAL_REGISTRY P
 INNER JOIN PSPRSMDEFN P_ONE
    ON P.PORTAL_NAME = P_ONE.PORTAL_NAME
   AND P.PORTAL_REFTYPE = 'F'
   AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME
 WHERE P_ONE.PORTAL_PRNTOBJNAME != ' ' )
   
SELECT PORTAL_NAME
     , PORTAL_OBJNAME
     , PORTAL_REFTYPE
     , PATH
     , PORTAL_LABEL
  FROM PORTAL_REGISTRY
 WHERE PORTAL_REFTYPE != 'F'
   AND PORTAL_NAME = 'EMPLOYEE'
   AND PORTAL_URI_SEG1 = 'UTILITIES'
   AND PORTAL_URI_SEG2 = 'PSOPTIONS'
   AND PORTAL_URI_SEG3 = 'GBL';

Notice that the main table expression remains unchanged. For each of the scenarios, I'm just manipulating the query that selects from the table expression. Are you interested in learning more about Common Table Expressions? Here is a list of posts I used to gain a better understanding of this feature:

• I Heart Recursive Subquery Factoring (Recursive Common Table Expressions)
• Recursive subquery factoring
• Recursive Queries Using Common Table Expressions

Base64 Encoding for PeopleSoft

This week on the ittoolbox peopeltools-I forum, I ran across a question on base64 encoding binary data using PeopleCode. There are a couple of ways to base64 encode data, none of which are delivered. Before deciding which method to use, we first have to answer two questions:

1. Where is the data (file, database, text string)?
2. Is the data in binary or plain text format?

If the data resides in the database, then we can select it out using the following SQL:

SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('Hello World'))) FROM DUAL;

In fact, rewrite that as:

SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(%TextIn(:1)))) FROM DUAL;

And, if you are an Oracle database user, then you have a generic SQL statement you can use to base64 encode any plain text. If you name the SQL definition BASE_64_ENCODE, then you can call that SQL definition as follows:

Local string &source = "Hello World";
Local string &encoded;
Local number &i;

SQLExec(SQL.BASE_64_ENCODE, &source, &encoded);
MessageBox(0, "", 0, 0, "base64: " | &encoded);

The PeopleCode and SQL above will work for any text string as long as you are using an Oracle database. Now, what if you want to base64 encode binary data? If the binary data is in the database, then modify the SQL accordingly.

Did you notice the %TextIn Meta-SQL I sneaked into the SQL above? Use it whenever you are sending large amounts of text to the database. For a string as short as "Hello World," you don't need it, but if you are trying to base64 encode an entire XML document, then you might.

What if you are not using Oracle database? Perhaps T-SQL has a base64 encoding routine? If so, great. If not, then the following code provides a Java/PeopleCode solution. Unfortunately, the 2 common Java base64 encoding algorithms use method and constructor overloads in a manner that requires some ugly Java reflection PeopleCode.

REM ** create an instance of the Java base64 encoder;
Local JavaObject &encoder = CreateJavaObject("sun.misc.BASE64Encoder");

REM ** get a reference to a Java class instance for the primitive byte array;
Local JavaObject &arrayClass = GetJavaClass("java.lang.reflect.Array");
Local JavaObject &bytArrClass = &arrayClass.newInstance(GetJavaClass("java.lang.Byte").TYPE, 0);

REM ** use reflection to get a reference to the method we want to call;
Local JavaObject &encodeArgTypes = CreateJavaObject("java.lang.Class[]", &bytArrClass.getClass());
Local JavaObject &encodeMethod = &encoder.getClass().getMethod("encode", &encodeArgTypes);

REM ** call the method;
Local JavaObject &bytes = CreateJavaObject("java.lang.String", "Hello World").getBytes();
Local JavaObject &result = &encodeMethod.invoke(&encoder, CreateJavaObject("java.lang.Object[]", &bytes));

REM ** print the result;
MessageBox(0, "", 0, 0, "Result: " | &result.toString());

If you know enough about Java to compile a wrapper class and you have access to your app server's class directory, then you can eliminate the Java reflection code in favor of a wrapper:

package com.blogspot.jjmpsj;

import java.io.IOException;

import sun.misc.BASE64Encoder;

public class Base64Coder {
    public static String encodeString(String data) {
        BASE64Encoder encoder = new BASE64Encoder();
        return encoder.encode(data.getBytes());
    }

    public static String encodeBytes(byte[] data) {
        BASE64Encoder encoder = new BASE64Encoder();
        return encoder.encode(data);
    }
}

... and call it like so:

Local JavaObject &encoder = GetJavaClass("com.blogspot.jjmpsj.Base64Coder");
Local string &result = &encoder.encodeString("Hello World");

MessageBox(0, "", 0, 0, &result);

As you can see from this final example, when working with overloaded constructors and methods, Java wrappers dramatically simplify PeopleCode.

Each of the examples above uses "Hello World" as the text string. What is "Hello World" in base64? SGVsbG8gV29ybGQ=. You can find an online base64 encoder/decoder here.

I will have to leave the question of base64 encoding binary files for another day. I am on the East coast attending Collaborate '09 and I need to get some rest so I can be right as rain for the conference tomorrow morning.

Update May 4th, 2009 at 4:09 AM: In the comments to this post, devwfb suggests that developers use org.apache.commons.codec.binary.Base64
rather than sun.misc.BASE64Encoder because classes in the sun package are undocumented and unsupported. devwfb is right and I should have mentioned this fact in my original post. I chose to use sun's base64 encoder because it is delivered and doesn't require customers to add more jars to the $PS_HOME/class directory. I will post the commons-codec example and link to it from here. See Sun's FAQ for more information.

Productivity gains through Meta-SQL

I hope the designer of PeopleSoft's Meta-SQL is extremely wealthy. I believe Meta-SQL usage is one of the most under utilized PeopleTools development practices. Do you want to improve your productivity as a PeopleSoft developer? Learn Meta-SQL. Consider the %InsertSelect Meta-SQL statement... A PeopleSoft SQL insert into/select from statement may contain 100 or more fields. Maintaining the mappings between the insert clause and the select clause can be quite daunting. %InsertSelect eliminates this mapping nightmare through convention: source fields with the same name are automatically mapped to destinations with the same name. If your scenario deviates from this convention, then a minor configuration in the Meta-SQL statement allows you to override the default behavior.

Likewise, using Meta-SQL, it is possible to write generic SQL that works regardless of the target table. Consider the following PeopleCode:

Local Record &rec = CreateRecord(Record.xxx);
Local string &exists;

REM set &rec key field values, copy from component buffer, etc;

SQLExec("SELECT 'X' FROM %Table(:1) WHERE %KeyEqual(:1)", &rec, &exists);

No matter what table I specify, this same SQL statement will tell me if a matching row exists in that table. PeopleSoft includes many of these Meta-SQL shortcuts. By combining a couple of Meta-SQL statements into a FUNCLIB, I can create a generic PeopleCode compliment to the Oracle merge statement (some call it UPSERT):

Function Merge(&rec As Record)
   Local string &exists;
   
   SQLExec("SELECT 'X' FROM %Table(:1) WHERE %KeyEqual(:1)", &rec, &exists);
   
   If (All(&exists)) Then
      SQLExec("%Update(:1)", &rec);
   Else
      SQLExec("%Insert(:1)", &rec);
   End-If;
End-Function;

Where would I use a merge function like this? Let's say you need to clone data in the current buffer, but change a key field (EFFDT perhaps?). Using the Copy methods of stand-alone Rowsets and Records, I can copy the component buffer into stand-alone rowsets and records, change the key fields, and then, with a generic loop, iterate over the rows and records, inserting or updating database values using the Merge function above.

There are several Meta-SQL routines. Some exist to provide database independence, but many exist to provide meta-data driven shortcuts for repetitive tasks. I encourage you to take some time to review the PeopleBooks Meta-SQL documentation. I trust you will be pleasantly surprised with the productivity gains you can achieve through Meta-SQL. Now, if I could just get someone to create an open source port of PeopleSoft's Meta-SQL that I can use with JDBC...

Query for Component and/or CREF Navigation

I regulary have to figure out the menu/portal navigation to a component given nothing more than a component or a CREF name (AKA Content Reference or Portal Object Name). For example, looking at a Pagelet definition in the Pagelet Wizard, you may see the cryptic content reference name for a component, but nothing telling you how to navigate to that component and no hint to help you find it in the portal registry. Likewise, a user might give you the menu and component name of a troubled component, but not the navigation to that component. It is situations like this that we, as developers, can realize value from the PeopleTools meta-data. Since the navigation, CREF name, and menu/component relationship is stored in the PeopleTools database, we can query a tools table to determine this navigation. The following Oracle specific SQL displays the navigation to a given CREF name. If you use a different database, and are familiar enough with your database's SQL, please translate this statement and post it as a comment for the benefit of the rest of the community.

WITH PORTAL_REGISTRY AS (
SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL), ' >> ')), ' >> ') PATH
     , LEVEL LVL
  FROM PSPRSMDEFN
 WHERE PORTAL_NAME = 'EMPLOYEE'
 START WITH PORTAL_OBJNAME = :1
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME )
SELECT PATH
  FROM PORTAL_REGISTRY
 WHERE LVL = (
SELECT MAX(LVL)
  FROM PORTAL_REGISTRY )

NOTE: Even though I've written my fair share of SQL, I'm no expert. Please don't grade me on the elegance and efficiency of the previous statement. If you are an SQL expert, and have a better way of writing this recursive SQL statement, then, please, for the benefit of the community, paste that statement into a comment. That said, I'll continue with the topic at hand...

If we pass EP_STANDARD_CF_TMPLT_GBL as bind :1, then the result will be Root >> Set Up Financials/Supply Chain >> Common Definitions >> Design ChartFields >> Configure >> Standard Configuration.

Modifying the above SQL statement a little, we can determine the navigation for a given menu, component, and market (the Ctrl-J data our users usually give us):

WITH PORTAL_REGISTRY AS (
SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL), ' >> ')), ' >> ') PATH
     , LEVEL LVL
  FROM PSPRSMDEFN
 WHERE PORTAL_NAME = 'EMPLOYEE'
 START WITH PORTAL_REFTYPE = 'C'
   AND PORTAL_URI_SEG1 = :1
   AND PORTAL_URI_SEG2 = :2
   AND PORTAL_URI_SEG3 = :3
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME )
SELECT PATH
  FROM PORTAL_REGISTRY
 WHERE LVL = (
SELECT MAX(LVL)
  FROM PORTAL_REGISTRY )

If we pass DESIGN_CHARTFIELDS for bind :1 (menu), STANDARD_CF_TMPLT for bind :2 (component), and GBL for bind :3 (market), then we will get the same result as the previous query, but using the Ctrl-J data rather than the CREF name.