HCM SOA Registry - Demystified

If you have worked on either HCM or Campus Solutions applications in PeopleSoft then you would have come across the term 'HCM SOA Registry'. Associated with the HCM SOA Registry is a step/process to 'Refresh Cache' which can either be done online or batch.

I have seen many customers (using HCM or Campus Solutions) who run this 'Refresh Cache' on a regular basis and sometimes as a post step to standard migrations. By standard migrations, I mean the weekly/fortnightly migrations which are part of production support release cycles and not necessarily the bundle/patch/upgrade migrations.

What is wrong with running this process regularly as a post step to any migration?

Well, nothing. But of late, I am finding several cases where the 'Refresh Cache' process fails with "RowsetCache::Save: Save failed for xxx/xxx=: unknown error. (2,975)" error particularly since HRMS 9.0 Bundle 26 (last HR bundle for CS).

Since this online/batch cache refresh has been causing a lot of pain, I wanted to find out when it is appropriate to refresh cache (reverse engineering and identifying the events - object changes - that would necessitate a refresh). I ended up building a SQL query to identify whether a migration of a project (say part of weekly/fortnightly production support release) would require a 'HCM SOA - Refresh Cache' just based on the objects it contains.

Before I share the details of the SQL query, I would like to share some of my notes since I did not find any information that was straight forward or easy to understand specifically on HCM Registry - Refresh Cache.

HCM SOA Registry:

HCM SOA Registry, like any other SOA Registry, provides a mechanism for service registration, discovery and binding.

SOA Registry Example:

Reference: Web 2.0 Architectures

HCM SOA Registry:

PeopleBooks Reference: Understanding the HCM Interface Registry

My Process/Flow Diagram: HCM SOA Registry

How do we access HCM SOA Registry?

Main Menu > Set Up HRMS > System Administration > HCM Registry

Service Registry:

Transformation Registry:

Where does HCM SOA Registry data get stored?

According to Doc ID 658098.1: HCM Service Framework Error When Adding, Updating, or Viewing Person Data in Campus Solutions:
"The HCM Service Registry (Set Up HRMS > System Administration > HCM Registry > Service Registry) captures the meta-data about the HCM SOA services: input and output signatures, implementation application classes, web services, component interfaces, etc. This meta-data is stored on the HC_REGISTRY portal (PeopleTools > Portal). This portal contains no other data."

How do we access this HC_REGISTRY portal where the HCM SOA Registry is stored?

- Navigate to Portal – Structure and Content: Main Menu > PeopleTools > Portal > Structure and Content.
- Replace the default portal (EMPLOYEE) with HC_REGISTRY in the URL.

For example:

https://www.test.edu/psp/ps/EMPLOYEE/HRMS/c/PORTAL_ADMIN.PORTAL_OBJ_LIST.GBL
>>>>
https://www.test.edu/psp/ps/HC_REGISTRY/HRMS/c/PORTAL_ADMIN.PORTAL_OBJ_LIST.GBL

HCM SOA Registry Hierarchy:

PeopleSoft Rowset Cache and how is it used by HCM SOA Registry?

- PeopleTools stores application data in a database cache to increase system performance.
- The RowsetCache contains a rowset, and serializes the contained rowset to binary form for speed.
- The RowsetCache class enables you to access this memory structure, created at runtime, and shared by all users.
- As with most PeopleTools objects, RowsetCache objects are cached to memory and file, but they are also cached to the data base.
- Rowset cache is stored in PSCONTDEFN and PSCONTENT.

PeopleBooks Reference:
PeopleSoft Rowset Cache
Using PeopleSoft Rowset Cache

According to Doc ID 658098.1: HCM Service Framework Error When Adding, Updating, or Viewing Person Data in Campus Solutions:
"The meta-data stored in the HC_REGISTRY portal is needed at run-time in order to execute the service. To speed up the retrieval of this meta-data at run-time, it is stored in cache, using PeopleTools Rowset Cache objects."

Layers of HCM Registry Metadata Caching:

App Server Cache*: As with most PeopleTools objects, Rowset cache also gets cached at the app server layer.

HCM Registry - Online Refresh Cache:

• Main Menu > Setup HRMS > System Administration > HCM Registry > Service Registry > Refresh Cache
• Refreshes HCM SOA Rowset Cache (PSCONTDEFN and PSCONTENT) with current HCM SOA metadata (HC_REGISTRY Portal).

HCM Registry - Batch Refresh Cache:

• Main Menu > Setup HRMS > System Administration > HCM Registry > Refresh HCM SOA Portal Access.
• Refreshes security on HC_REGISTRY portal.
• Makes sure all folders and CREFs in HC_REGISTRY are PUBLIC.
• DOES NOT add any roles. Adding a default role to all users is now a deprecated functionality. Refer: Doc ID 1370324.1 - FAQ on Refresh HCM SOA Portal Access - Question 1.
• Refreshes HCM SOA Rowset Cache (PSCONTDEFN and PSCONTENT) with current HCM SOA metadata (HC_REGISTRY Portal).

SQL to identify if a migration (list of projects) requires a HCM SOA - Refresh Cache:

Based on the HCM SOA Registry Hierarchy diagram above, we can see that all metadata (HC_REGISTRY Portal) and interfaces (App Classes, SQL Views, Messages) are PeopleTools Managed Objects. So the below SQL looks at PeopleTools metadata to figure out if a list of projects (part of a release) contain any objects associated with HCM SOA Registry.

-- Test Value for &&1: 'HCM900_MP11_D','PRJ812865','PRJ889693','PRJ889694','PRJ882259'

/* HCM Registry Objects - Content References */
SELECT 'HC Registry objects (CREFs) found in release. Run SOA Refresh Cache.' AS Found,
  'HC_REGISTRY'                                                               AS OBJECT,
  OBJECTVALUE3                                                                AS PORTAL_OBJNAME
FROM PSPROJECTITEM A
WHERE A.projectname IN (&&1)
AND A.OBJECTTYPE     = 55
AND OBJECTVALUE1     = 'HC_REGISTRY'
UNION

/* Interface Objects - Application Classes (Services, Types, Exceptions) */
SELECT 'HCM metadata objects (services/types/exceptions) found in release. Run HCM SOA Refresh Cache.' AS Found,
  TO_CHAR(I.PORTAL_ATTR_VAL)                                                                           AS OBJECT,
  I.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL I
WHERE I.PORTAL_NAME   = 'HC_REGISTRY'
AND I.PORTAL_REFTYPE  = 'C'
AND I.PORTAL_OBJNAME IN
  (SELECT A.PORTAL_OBJNAME
  FROM PSPRSMSYSATTRVL A
  WHERE A.PORTAL_NAME   = 'HC_REGISTRY'
  AND A.PORTAL_REFTYPE  = 'C'
  AND A.PORTAL_ATTR_NAM = 'TYPE'
  AND TO_CHAR(A.PORTAL_ATTR_VAL) IN ('EXC', 'SRV', 'TYP')
  )
AND I.PORTAL_ATTR_VAL           like '%:%'
AND TO_CHAR(I.PORTAL_ATTR_VAL) IN
  (SELECT REPLACE (A.objectvalue1 | | ':' | | A.objectvalue2 | | ':' | | A.objectvalue3 | | ':' | | A.objectvalue4, ': ')
  FROM PSPROJECTITEM A
  WHERE A.projectname IN (&&1)
  AND objecttype       = 58
  )
UNION

/* Interface Objects - Views */
SELECT 'HCM metadata objects (views) found in release. Run HCM SOA Refresh Cache.' AS Found,
  TO_CHAR(I.PORTAL_ATTR_VAL)                                                       AS OBJECT,
  I.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL I
WHERE I.PORTAL_NAME   = 'HC_REGISTRY'
AND I.PORTAL_REFTYPE  = 'C'
AND I.PORTAL_OBJNAME IN
  (SELECT A.PORTAL_OBJNAME
  FROM PSPRSMSYSATTRVL A
  WHERE A.PORTAL_NAME             = 'HC_REGISTRY'
  AND A.PORTAL_REFTYPE            = 'C'
  AND A.PORTAL_ATTR_NAM           = 'TYPE'
  AND TO_CHAR(A.PORTAL_ATTR_VAL) IN ('IVW')
  )
AND PORTAL_ATTR_NAM             = 'IMPLEMENTATION'
AND TO_CHAR(I.PORTAL_ATTR_VAL) IN
  (SELECT REPLACE (A.objectvalue1 | | ':' | | A.objectvalue2 | | ':' | | A.objectvalue3 | | ':' | | A.objectvalue4, ': ')
  FROM PSPROJECTITEM A
  WHERE A.projectname IN (&&1)
  AND objecttype       = 0
  )
UNION

/* Interface Objects - HCM Transformation Maps */
SELECT 'HCM metadata objects (messages) found in release. Run HCM SOA Refresh Cache.' AS Found,
  TO_CHAR(I.PORTAL_ATTR_VAL)                                                          AS OBJECT,
  I.PORTAL_OBJNAME
FROM PSPRSMSYSATTRVL I
WHERE I.PORTAL_NAME   = 'HC_REGISTRY'
AND I.PORTAL_REFTYPE  = 'C'
AND I.PORTAL_OBJNAME IN
  (SELECT A.PORTAL_OBJNAME
  FROM PSPRSMSYSATTRVL A
  WHERE A.PORTAL_NAME             = 'HC_REGISTRY'
  AND A.PORTAL_REFTYPE            = 'C'
  AND A.PORTAL_ATTR_NAM           = 'TYPE'
  AND TO_CHAR(A.PORTAL_ATTR_VAL) IN ('TRF')
  )
AND PORTAL_ATTR_NAM            IN ('MSGNAME', 'TO_MSGNAME')
AND TO_CHAR(I.PORTAL_ATTR_VAL) IN
  (SELECT OBJECTVALUE1
  FROM PSPROJECTITEM A
  WHERE A.projectname IN (&&1)
  AND A.OBJECTTYPE     = 37
  );

If the query returns results then we need to run HCM SOA - Refresh Cache. If not, there is no need.

Note: This post is only focusing on the need (if any) for running the HCM SOA - Refresh Cache as a post step to migrations. This does not mean that there would not be other circumstances (outside of migrations) where the Refresh Cache might be necessary.

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';

AAWS - Targeted Error Handling

While troubleshooting AAWS issues (Admission Applications Web Services delivered in Campus Solutions) in a Production Environment, I found the following options that might come in handy.

Let us take AAWS - Submit Application (SAD_SUBMITAPPL) service operation as an example for this blog. Note: The same approach could be extended to any other AAWS service operation.

The problem or difficulty with troubleshooting this service operation is that the faults generated are very generic in nature and does not help with identifying the root cause of the problem.

Here is an example of a fault message generated by SAD_SUBMITAPPPL:

<SOAP-ENV:Envelope xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
  <SOAP-ENV:Body>
    <SOAP-ENV:Fault>
      <faultcode>Client</faultcode>
      <faultstring>An Error occurred processing this request (14098,286)</faultstring>
      <detail>
        <MSGS>
          <MSG>
            <ID>14098-206</ID>
            <DESCR>Error during Application submit, Contact the Administrator</DESCR>
            <MESSAGE_SEVERITY>I</MESSAGE_SEVERITY>
            <PROPS/>
          </MSG>
        </MSGS>
      </detail>
    </SOAP-ENV:Fault>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

While I can understand why AAWS faults are generic, potentially to mask the nasty error messages from being displayed to the end user, it is my personal opinion that this type of fault message is unacceptable especially for a high volume web service such as AAWS (from a service maintenance point of view). Let us explore how we can put controls in place to be able to identify the root cause for this error in real-time.

Using Delivered Campus Service Oriented Architecture Logging (Doc ID 1511543.1):

This document details couple of options.

1. Setting up the regular logging at the routing level. This does not really add much value in the case of AAWS.
   
   Logging:
   
   
   
   Results:
   
   
   
   You will notice that there are no errors logged for the synchronous transaction.
   
   
2. Setting Log Type and Threshold using delivered Campus Service Oriented Architecture (SOA) Logging.
   
   
   
   We can set the Logging Type to Database or File. For this example I chose the Database option. For the Log Threshold we have many options. The 'All' option would provide a lot of detail and something like 'Severe' option would probably give us what we want (in terms of errors).
   
   The result of this logging should be available in the PS_SCC_INTEG_LOG table. Use the following SQL to get the most recent entries first:
   
   select * from PS_SCC_INTEG_LOG order by 1 desc;
   
   You will notice that SCC_INT_LOG_MSG (where SCC_INT_LOG_LEVEL=2) would contain the error message that we are after. In my case, I found that my error message was as follows:
   
   "Transaction Manager Exception: Error Saving: XXX_CUSTOM_STG tempid: 579 (0,0) SCC_COMMON.ENTITY.StagedEntity.OnExecute  Name:save  PCPC:16872  Statement:322 Called from:SCC_COMMON.ENTITY.ChildEntity.OnExecute  Name:save  Statement:101 Called from:SCC_C"
   
   Now, this would tell us exactly why and where (peoplecode statement) the error occurred. This helps greatly in comparison to the Generic Error Message (Error during Application submit, Contact the Administrator).
   
   While this is very useful for short term/unexpected troubleshooting, one downside of the Campus SOA Logging is that it is not specific to a service operation. So if we turn on logging it would take effect for all delivered service operations that are represented using the Campus SOA. You will notice that if we have this logging turned on in a Production Environment then the table PS_SCC_INTEG_LOG would quickly fill up. Another scenario where this logging might fall short is if we want to send back real-time meaningful response/fault messages for the consumer to process and take action.

Now that we have exhausted delivered options (please correct me if I missed anything!), let us look at a targeted error handling approach (returning appropriate exceptions in the fault message) involving a minor customization. Note: While customizations would involve cost and maintenance considerations, it is generally the case of whether the benefits outweigh the effort and maintenance. I will let you make the decision based on the circumstances in your organization.

AAWS Targeted Error Handling - Customizing the Handler:

The OnRequest Handler code for the AAWS - SAD_SUBMITAPPL is implemented in the following class and method:

Class: SCC_OLA.HANDLERS.Admissions.OnExecute

Method: submitApplication

Let us take a look at a snippet of the delivered code that deals with part of the error handling which I customized:

Here is the same snippet of delivered code with my customization:

Let us examine my customization in detail:

         /* XXX - SV - 20141013 - Test Start */

         Local SCC_COMMON:ENTITY:LOG:MessageEntry &msgEntryCust = create SCC_COMMON:ENTITY:LOG:MessageEntry();

         Local array of string &parms = CreateArrayRept("", 0);

         &parms [1] = &e2.ToString();

         &msgEntryCust.DataPopulateV1(14098, 181, &msgEntryCust.Severity_Error, &parms, "", "", "");

         &returnError.writeEntry(&msgEntryCust);

         /* XXX - SV - 20141013 - Test End */

I made use of the MessageEntry class to create another message detail (<MSG> section), populate the message detail with the exception information and append it to the &returnError object which is the fault message.

Note: I used the same message set number and message number (14098, 181) that is referenced in the logError method right at the start of this exception handling section. 

&p_logger.logError(MsgGet(14098, 181, "", &e2.ToString()));

Let us see the results of this customization.

<SOAP-ENV:Envelope xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"> 

  <SOAP-ENV:Body> 
    <SOAP-ENV:Fault> 
      <faultcode>Client</faultcode> 
      <faultstring>An Error occurred processing this request (14098,286)</faultstring> 
      <detail> 
        <MSGS> 
          <MSG> 
            <ID>14098-206</ID> 
            <DESCR>Error during Application submit, Contact the Administrator</DESCR> 
            <MESSAGE_SEVERITY>I</MESSAGE_SEVERITY> 
            <PROPS/> 
          </MSG> 
          <MSG> 
            <ID>14098-181</ID> 
            <DESCR>Transaction Manager Exception: Error Saving: XXX_CUSTOM_STG tempid: 583 (0,0) SCC_COMMON.ENTITY.StagedEntity.OnExecute  Name:save  PCPC:16872  Statement:322
Called from:SCC_COMMON.ENTITY.ChildEntity.OnExecute  Name:save  Statement:101
Called from:SCC_COMMON.ENTITY.StagedEntity.OnExecute  Name:saveChildren  Statement:374
Called from:SCC_COMMON.ENTITY.StagedEntity.OnExecute  Name:save  Statement:303
Called from:SAD_ADM_APPL.ApplicationManager.OnExecute  Name:saveApplication  Statement:70
Called from:SCC_OLA.HANDLERS.Admissions.OnExecute  Name:submitApplication  Statement:595
Call</DESCR> 
            <MESSAGE_SEVERITY>E</MESSAGE_SEVERITY> 
            <PROPS/> 
          </MSG> 
        </MSGS> 
      </detail> 
    </SOAP-ENV:Fault> 
  </SOAP-ENV:Body> 
</SOAP-ENV:Envelope>

Highlighted in orange is the marker for the custom message that was added and highlighted in green are the details of the custom message.

You can see how we can improve the details in the fault messages enabling both technical and functional staff to quickly identify the problem with the application and take corrective action. This is very effective especially when there could be application submission peaks where we can expect thousands of application requests in a day.

Note:

• Please test (test and test!) this thoroughly before applying the suggested customization to your Production Environment (if you think this might be useful).
• In the submitApplication method used in this example there are a 4 occurrences of &returnError.writeEntry method being invoked. It is recommended that each of those are appropriately extended using the approach in the sample code and example provided in this blog.
• Customization Environment Details: Campus Solutions 9.0 (Bundle 34) and PeopleTools 8.52.22.