PSPRSMNAVINFO - Portal Menu Navigation in SQL

Last week, I worked on a requirement where I needed to find all the components that are part of a role (or a list of roles) including the Portal Menu Navigation Path if it exists. It is easy to get the list of components that are part of a role but it is not really straight forward to qualify that information with the actual Portal Menu Navigation (if it exists).

 In the past, I used the following query to return the Portal Menu Navigation Path for a Component. The query mines through the Portal Structure and Content and presents the navigation hierarchy in the result.

As we can see, it will not be easy to re-purpose this hierarchical query and expand it for my requirement.

While poking around on this topic, I stumbled on this gem of a table PSPRSMNAVINFO that, I believe, was introduced thanks to the PeopleSoft Search Framework!

Here is what we can see in the Definition Properties of this record:
"Holds the Navigation Path to the given target crefs registered in Portal Registry. Holds only effective and non-expired target crefs.
Used by the Portal Menu SES Search.
Updated every schedule of search index file"

From my understanding, it appears that this table is used as part of the PeopleSoft Search Framework - PTPORTALREGISTRY Search Definition. Also, the App Engine library PORTALPATHAE is responsible for populating this table with the navigation path information when the search index is built.

PeopleTools > Search Framework > Designer > Search Definition (PTPORTALREGISTRY)

PeopleTools > Search Framework > Administration > Schedule Search Index

To identify the last time the PTPORTALREGISTRY search definition index was built, we can query the table PSPRSMPATHSTAT.

If for any reason we don't have the Search Framework enabled and we still want to take advantage of the data in this table, then we can simply populate this table by directly running the PORTALPATHAE App Engine Library from App Designer. Since I was testing this on a Virtual Box installation of a PUM image (CS 9.2 - Image 4), I did not have Search enabled/configured. I ran the AE from App Designer as follows:

Now, let us see how we can use PSPRSMNAVINFO to get the Portal Menu Navigation information.

The best part about this table is that it allows us to directly retrieve the navigation path for a component without having to deal with the hierarchical structure in PSPRSMDEFN. This makes it easier to use/expand this information in other queries.

Here is how I extended this information to create a query that retrieves all components in a role along with the Portal Menu Navigation Path information (if it exists).


Github link to SQL Queries in this post:
https://github.com/SasankVemana/PS-Portal-Menu-Navigation-Queries

DIY - PeopleSoft on the Cloud - Part 3

In this third and final part, I will demonstrate how to access the demo instance (PUM image on OPC) via SQL Developer and App Designer from an on premise workstation.

Step 1: Create Security List for database access and add to the instance

Create Security IP List:

Oracle Compute Cloud Service console > Network tab > Security IP Lists > Create Security IP List

Enter the public ip address(es) of your on premise workstation(s). This will ensure that only a trusted list of on premise ip addresses can access the database (via SQL Developer/App Designer) of your demo instance (PUM image running on OPC).

Note: If you are not sure of the public ip address of your on premise workstation then simply visit https://whatismyipaddress.com/ using a browser on the workstation.

Create Security Application:

Oracle Compute Cloud Service console > Network tab > Security Applications > Create Security Application

Create a Security Application to open TCP port 1522 to allow database access. You might wonder how I figured the database for the demo instance is using port 1522? We can find it in the tnsnames.ora file located in /opt/oracle/psft/pt/tools_client/ which we can access via WinSCP (refer: Part 2).

Create Security List:

Oracle Compute Cloud Service console > Network tab > Security Lists > Create Security List

Create Security Rule:

Oracle Compute Cloud Service console > Network tab > Security Rules > Create Security Rule

Using the Security IP List, Security Application and Security List that we just created, create a Security Rule as follows:

Add Security List to Instance:

Oracle Compute Cloud Service console > Instances tab > View > Under "Security Lists" > Add to Security List

In my example, the security list that I added is called 'psftuser_sec_list_1522'.

Step 2: Installing Oracle and PeopleTools Clients (SQL Developer and App Designer)

Hypothetically, if we had installed the PUM image locally and if we did not already have the Oracle and PeopleTools clients installed on the local workstation, then we would have mapped the samba drive and installed the clients from there.

I was not able to figure out how to map the samba drive of the demo instance (PUM image) running on OPC to an on premise workstation. The alternative/workaround is to connect to the instance on OPC using WinSCP from the on premise workstation (as described in Part 2), download the relevant folders locally and install the necessary clients.

We can find the content of the samba drive under the following location using WinSCP:
 /opt/oracle/psft/pt/tools_client

Step 3: Updating the tnsnames.ora file in the on premise work station

Find the database name of the instance:

Login to the instance online and navigate to the following page:
Navigator > PeopleTools > Lifecycle Tools > Update Manager > Update Manager Dashboard > Update Manager Utilities > About PeopleSoft Image

Add database to the tnsnames.ora file on the on premise workstation:

For a typical Oracle client installation, the tnsnames.ora file can be found in the following directory:
C:\oracle\product\12.1.0\client_1\network\admin

TNS Details:

HOST: Enter the public ip address of your instance
PORT: 1522
SERVICE_NAME: Enter the database name of your instance which we determine in the preceding steps

<ENTER_DBNAME> =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = <ENTER_PUBLIC_IP>)(PORT = 1522))
       )
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = <ENTER_DBNAME>)
       )
    )

For Example:

Step 4: Access OPC instance via SQL Developer from on premise workstation

Default Username/Password: SYSADM/SYSADM

Connection Properties:

Successful Connection:

Step 5: Access OPC instance via App Designer from an on premise workstation

Note: The default connect id and password is the same as people/peop1e

If you have not configured the default connect id/password then you can do so using Configuration Manager.

The PeopleTools client installation should be in your C drive.
For example: C:\PT8.55.11_Client_ORA\

We can find Configuration Manager (pscfg.exe) and App Designer (pside.exe) in the following location:
C:\PT8.55.11_Client_ORA\bin\client\winx86\

Connect via App Designer:

Use the database name of your OPC instance which we determined in the preceding steps.

Successful Connection:

Oracle SQL: REGEX_REPLACE Function

Recently, I participated in a forum discussion (Higher Education User Group) where someone asked if there is a way (and if so how) to strip out HTML elements from a long character field. This is mainly for scenarios where a long character field is used to store rich text enabled data (which contains HTML elements). And while querying the data from this field either using PS Query or SQL the requirement is to only retrieve the "plain text".

I found that we could use a delivered Oracle SQL function called REGEX_REPLACE to pattern match and replace contents of a string.

Here are some examples of how to use this function:

If we want to mainly get rid of HTML elements then we can use the following pattern:

select REGEXP_REPLACE(DESCRLONG, '<[^>]+>|\&(nbsp;)|\&(nbsp)|(amp;)', '',1,0, 'i'), DESCRLONG from PSMSGCATDEFN where descrlong like '%<%>%';

If we additionally want to get rid of contents inside script/style elements then we can use the following pattern:

select REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(DESCRLONG, '<script[^>]*>.*</script>', '',1,0,'in'), '<style[^>]*>.*</style>', '',1,0,'in'),'<[^>]+>|\&(nbsp;)|\&(nbsp)|(amp;)', '',1,0,'i'), DESCRLONG from spsoft.PSMSGCATDEFN where descrlong like '%<%>%';

Note:
- You will need to improvise the regex pattern for those cases which are not trapped in the above sample SQLs.
- This function REGEX_REPLACE only works on Oracle databases.
- I have to admit that I am not a RegEx expert and received help from some very talented colleagues who helped me with the second (more complex) SQL.

HCM/Campus Solutions Address Validations

If anyone has worked on a CI or a web service (e.g.: CWS - Constituent Web Service, AAWS - Admission Applications Web Service) that involves loading data related to addresses into HCM/Campus Solutions applications then you must have run into the address validation problem. The problem with the validation on the addresses component/page is that the fields displayed on the address subpage (specific to a country) would vary depending on the country.

This complicates the process of dynamically identifying the list fields that need to be populated depending on the current country that is being used for data entry. Let us take the example of the Admission Applications Web Service to put things into perspective. Typically, there would a web front-end for the admission applications and that would in turn invoke the AAWS web service to create and save applications into Campus Solutions. Now, the problem for the web front-end application is to dynamically figure out the list of fields that need to be populated on the AAWS request message based on the current country. Once we determine the list of fields that need to be populated, we would additionally run into the problem where the validation on the fields (required/translate/prompt edits) would differ per country.

Here are a list of logical steps and SQLs that would help with building some of that dynamic address validation logic. I was able to write some SQLs that look into PeopeTools metadata and pull out the information we need.

E.g.:

Address Formatting:

To identify the list of fields to display and populate (CIs/Web Service calls) data use PS_EO_ADDR_FMT_TBL.

E.g.:

select * from PS_EO_ADDR_FMT_TBL where COUNTRY = 'ESP';

The address format data could be found online as well.

Address Validations:

Required Fields:

Only the following fields are required:
-    COUNTRY
-    ADDRESS1* or ADDRESS2* or ADDDRESS3* or ADDRESS4*: At least one of these fields is required depending on how many exist in SQL¹.

* The SQL¹ provided must return the fields listed.

E.g.:

For ESP (Spain), SQL¹ only returns ADDRESS1 and ADDRESS2 fields. Therefore, at least one of ADDRESS1 or ADDRESS2 fields is required for Spain.
For BRA (Brazil), SQL¹ returns all fields ADDRESS1, ADDRESS2, ADDRESS3 and ADDRESS4. Therefore, at least one of ADDRESS1 or ADDRESS2 or ADDRESS3 or ADDRESS4 fields is required for Brazil.

Fields With Translate Edits:

The SQL¹ provided must return ‘Y’ for TRANSLATE_EDIT value for the corresponding COUNTRY, FIELDNAME.

E.g.:

ADDR_FIELD1 for Spain (ESP) is based on a translate edit.

Note: For List of Translates Values for a particular field use SQL².

Fields With Prompt Table Edits:

The SQL¹ provided must return ‘Y’ for PROMPT_EDIT value for the corresponding COUNTRY, FIELDNAME.

E.g.:

STATE field for BRA (Brazil) has a prompt table of STATE_TBL. The key fields on the prompt table are COUNTRY, STATE.
CITY field for BRA (Brazil) has a prompt table of CODE_CITY_BRA. The key fields on the prompt table are STATE, CITY.

Note: For List of Values for a particular field use the prompt table (PROMPT_TABLE) with the associated prompt table keys (PROMPT_TABLE_KEYS).

Other Scenario:

If a field exists in PS_EO_ADDR_FMT_TBL and does not fall into any of the above scenarios then there is NO field validation.

SQL¹ - Address Format Sub Page Information Based On PeopleTools MetaData:

SELECT Substr(A.pnlname, 9, 3) AS COUNTRY,
       A.fieldname,
       CASE
         WHEN Bitand(B.useedit, 512) > 0 THEN 'Y'
         ELSE 'N'
       END AS TRANSLATE_EDIT,
       CASE
         WHEN Bitand(B.useedit, 16384) > 0 THEN 'Y'
         ELSE 'N'
       END AS PROMPT_EDIT,
       edittable AS PROMPT_TABLE,
       (SELECT Listagg(Y.fieldname, ', ')
                 within GROUP (ORDER BY y.fieldnum) "PROMPT_TABLE_KEYS"
        FROM   psrecfield Y
        WHERE  Y.recname = B.edittable
               AND Bitand(Y.useedit, 1) > 0
        GROUP  BY Y.recname)   AS PROMPT_TABLE_KEYS
FROM   pspnlfield A,
       psrecfield B
WHERE  A.pnlname IN (SELECT X.pnlname
                     FROM   ps_eo_addr_subpage X)
       AND A.recname <> ' '
       AND A.fieldname <> ' '
       AND MOD(A.fielduse, 2) = 0
       AND A.recname = B.recname
       AND A.fieldname = B.fieldname
--and substr(A.PNLNAME, 9,3) = 'BRA'
ORDER  BY country,
          A.fieldnum; 

SQL² – List Translate Values For A Field:

SELECT A.fieldname,
       A.fieldvalue,
       A.xlatlongname  AS DESCR,
       A.xlatshortname AS DESCR_SHORT
FROM   psxlatitem A
WHERE  A.fieldname = 'ADDR_FIELD1'
       AND A.effdt = (SELECT Max(A_ED.effdt)
                      FROM   psxlatitem A_ED
                      WHERE  A.fieldname = A_ED.fieldname
                             AND A.fieldvalue = A_ED.fieldvalue
                             AND A_ED.effdt <= SYSDATE)
       AND A.eff_status = 'A';

Oracle SQL: String Aggregation to Convert Rows of Data into a Single Column - LISTAGG Function

I recently learnt about this cool Oracle SQL function called LISTAGG. I am sure some of you must have come across this function already. I found it very useful for a lot of cases to perform string aggregation to convert rows of data into a single column.

Here is an example to aggregate multiple rows of fields for a particular record (PSRECFIELD) into a single column.

SQL which returns multiple FIELDNAME rows per RECNAME on PSRECFIELD:

select RECNAME, FIELDNAME from PSRECFIELD where recname = 'PSOPRDEFN' ORDER BY FIELDNUM;

 
Now let us convert these rows of fields into a single column using string aggregation (LISTAGG).

SQL using LISTAGG which returns one row with all fields per RECNAME on PSRECFIELD:


select RECNAME, LISTAGG(FIELDNAME, ', ') WITHIN GROUP (ORDER BY FIELDNUM) "Field_Listing" from PSRECFIELD where recname = 'PSOPRDEFN' group by RECNAME;

We can see that the list of fields were all aggregated and separated by a comma. This function is very useful for several reasons. I use the above SQL specifically for selecting all columns from a particular table/view instead of having to type them out by hand.

E.g.: I can copy the results of Field_Listing column and use it in my select statement.

select OPRID, VERSION, OPRDEFNDESC, EMPLID, EMAILID, OPRCLASS, ROWSECCLASS, OPERPSWD, OPERPSWDSALT, ENCRYPTED, SYMBOLICID, LANGUAGE_CD, MULTILANG, CURRENCY_CD, LASTPSWDCHANGE, ACCTLOCK, PRCSPRFLCLS, DEFAULTNAVHP, FAILEDLOGINS, EXPENT, OPRTYPE, USERIDALIAS, LASTSIGNONDTTM, LASTUPDDTTM, LASTUPDOPRID, PTALLOWSWITCHUSER
from PSOPRDEFN;

Another good example:

SQL using LISTAGG to return list of key fields (comma separated) for particular record definition in a single row.


SELECT A.RECNAME, Listagg(A.FIELDNAME, ', ') within GROUP (ORDER BY A.FIELDNUM) "RECORD_KEY_FIELDS" FROM   PSRECFIELD A WHERE  A.RECNAME = 'NAMES' AND Bitand(A.USEEDIT, 1) > 0 GROUP  BY A.RECNAME;