Rowset Messages

Rowset messages are messages that are completely backed by records and objects. If your client is another PeopleSoft system or you just want to dump out the PeopleSoft tables to an external system this is the way to go.

Let’s imagine we want to create a message that dumps out some security tables for a user. The desired structure would be the following.

• PSOPRDEFN - Root record - only 1 rows
  • PSROLEUSER - Child rowset any number of children
    • PSROLECLASS - The permission lists on each role
  • PSOPRALIAS - Child rowset any number of children

We will create a message object and see how we can use this in a handler example.

• Warning: I generally do NOT like to use these for PeopleSoft inbound web services. External system may need to send XML attributes that are NOT on the record. If you include extra elements in the message the XML parsing for that row silently fails and you have no way to trap for it. I have lost days of my life chasing these errors down.

Creating the Message Object

First we need to create a new message object.

Choose type of Rowset and give it a name.

Now you will be in the message configuration page. We need to add a “Root” record object which will be the parent. Select the “Add Record to Root” link.

You will be prompted for a a record name. In our case, we are going to use the PSOPRDEFN record as our root record.

Now the record will be show in a tree structure at the bottom. If you click the link on the PSOPRDEFN record you can see the attributes of that record as well as add child records.

In the “Action” section you will see some options to add child records.

We will add several child records to create our desired hierarchy.

In order to make the PSOPRDEFN –> PSROLEUSER –> PSROLECLASS you need to click on the PSROLEUSER in the tree and insert the child record.

Now that we have our desired hierarchy of records in the message object let’s drill back into the PSOPRDEFN and look at some of the functionality we can do. In our example to follow we are going to be exporting out some PeopleSoft data to a third party.

By default the XML will mimic the structure of the underlying records and fields. We can selectively turn off some fields from being exported as well as alias some field names. There is also an option to wrap fields in a CDATA Element. We are going to exclude some sensitive password fields. Additionally, we will implement an alias and also wrap that aliased field in a CDATA element.

Creating a Service Operation

Now we need to create a service operation that is backed with this new Rowset Message. I will just show you the end result here since the steps are covered elsewhere in this book.

You should make note of the following:

• Our service operation has one parameter in the URL and we can pull that out using the OPRID template variable.

Coding the handler

Now we need to create our handler to respond to incoming requests, pull out the OPRID parameters that the client is asking for and return the data once it is pulled from the database.

import PS_PT:Integration:IRequestHandler;

class RowsetMessageExample1 implements PS_PT:Integration:IRequestHandler
   method onRequest(&msGRequest As Message) Returns Message;
   
end-class;

method onRequest
   /+ &msGRequest as Message +/
   /+ Returns Message +/
   /+ Extends/implements PS_PT:Integration:IRequestHandler.OnRequest +/
   
   
   Local Message &msgResponse;
   &msgResponse = CreateMessage(@("Operation." | &msGRequest.OperationName), %IntBroker_Response);
   
   Local Document &reqDOC = &msGRequest.GetURIDocument();
   Local string &paramOPRID = &reqDOC.GetElement("OPRID").value;
   
   
   If All(&paramOPRID) Then
      
      Local Rowset &rsPSROLECLASS = CreateRowset(Record.PSROLECLASS);
      /* This creates a nested rowset with PSROLEUSER --parent to -- PSROLECLASS */
      Local Rowset &rsPSROLEUSER = CreateRowset(Record.PSROLEUSER, &rsPSROLECLASS);
      Local Rowset &rsPSOPRALIAS = CreateRowset(Record.PSOPRALIAS);
      /* Passing in child rowsets as second and third parameter */
      Local Rowset &rsPSOPRDEFN = CreateRowset(Record.PSOPRDEFN, &rsPSROLEUSER, &rsPSOPRALIAS);
      
      
      If &rsPSOPRDEFN.Fill("where OPRID = :1", &paramOPRID) > 0 Then
         
         &rsPSOPRDEFN.GetRow(1).GetRowset(Scroll.PSOPRALIAS).Fill("WHERE OPRID = :1 ", &paramOPRID);
         If &rsPSOPRDEFN.GetRow(1).GetRowset(Scroll.PSROLEUSER).Fill("WHERE ROLEUSER = :1 ", &paramOPRID) > 0 Then
            Local integer &i;
            For &i = 1 To &rsPSOPRDEFN.GetRow(1).GetRowset(Scroll.PSROLEUSER).ActiveRowCount
               
               Local Row &rowPSROLEUSERFilled;
               &rowPSROLEUSERFilled = &rsPSOPRDEFN.GetRow(1).GetRowset(Scroll.PSROLEUSER).GetRow(&i);
               &rowPSROLEUSERFilled.GetRowset(Scroll.PSROLECLASS).Fill(" where ROLENAME = :1", &rowPSROLEUSERFilled.PSROLEUSER.ROLENAME.Value);
               
            End-For;
         End-If;
      End-If;
      
      
      &rsPSOPRDEFN.CopyTo(&msgResponse.GetRowset());
      
   End-If;
   
   Return &msgResponse;
   
end-method;

• Line 17-18: We are just grabbing the requested OPRID that the client wants information on.
• Line 23-28: Here we are creating a in memory rowset that has the hierarchy based on our rowset. Pay attention to how I create the grandchild first. Then pass that as a parameter to the child and then the parent.
• Line 31, 33, 40: We are actually running a “fill” method that will populate the in memory rowset with data.
• Line 47: We copy the in memory rowset to the message rowset. It will copy liked named records to the messages.

Testing and Inspecting the Output

Now let’s see what will be returned with our setup thus far. We will call the service operation and as for information on OPRID = “SF0019”.

The HTTP call looks like this:

GET http://testserver.dev/PSIGW/RESTListeningConnector/PSFT_CS/CHG_USER_PROFILE.v1/SF0019 HTTP/1.1
Authorization: Basic snip
Accept-Encoding: gzip

The HTTP response looks like this:

HTTP/1.1 200 OK
Connection: close
Date: Fri, 25 Oct 2019 18:09:22 GMT
Content-Length: 1047
Content-Type: text/xml; encoding="UTF-8"
Content-Encoding: gzip

<?xml version='1.0'?>
<CHG_SECURITY_USER>
  <FieldTypes>
    <PSOPRDEFN class="R">
      <OPRID type="CHAR"/>
      <USERIDALIAS type="CHAR"/>
      <OPRDEFNDESC type="CHAR"/>
      <EMPLID type="CHAR"/>
      <EMAILID type="CHAR"/>
      <OPRCLASS type="CHAR"/>
      <ROWSECCLASS type="CHAR"/>
      <LANGUAGE_CD type="CHAR"/>
      <MULTILANG type="NUMBER"/>
      <CURRENCY_CD type="CHAR"/>
      <ACCTLOCK type="NUMBER"/>
      <PRCSPRFLCLS type="CHAR"/>
      <DEFAULTNAVHP type="CHAR"/>
      <EXPENT type="NUMBER"/>
      <OPRTYPE type="NUMBER"/>
      <LASTSIGNONDTTM type="DATETIME"/>
      <LASTUPDDTTM type="DATETIME"/>
      <LASTUPDOPRID type="CHAR"/>
      <PTALLOWSWITCHUSER type="NUMBER"/>
    </PSOPRDEFN>
    <PSROLEUSER class="R">
      <ROLEUSER type="CHAR"/>
      <ROLENAME type="CHAR"/>
      <DYNAMIC_SW type="CHAR"/>
    </PSROLEUSER>
    <PSOPRALIAS class="R">
      <OPRID type="CHAR"/>
      <OPRALIASTYPE type="CHAR"/>
      <OPRALIASVALUE type="CHAR"/>
      <SETID type="CHAR"/>
      <EMPLID type="CHR"/>
      <CUST_ID type="CHAR"/>
      <VENDOR_ID type="CHAR"/>
      <APPLID type="CHAR"/>
      <CONTACT_ID type="CHAR"/>
      <PERSON_ID type="CHAR"/>
      <EXT_ORG_ID type="CHAR"/>
      <BIDDER_ID type="CHAR"/>
      <EOTP_PARTNERID type="NUMBER"/>
    </PSOPRALIAS>
    <PSROLECLASS class="R">
      <ROLENAME type="CHAR"/>
      <CLASSID type="CHAR"/>
    </PSROLECLASS>
    <PSCAMA class="R">
      <LANGUAGE_CD type="CHAR"/>
      <AUDIT_ACTN type="CHAR"/>
      <BASE_LANGUAGE_CD type="CHAR"/>
      <MSG_SEQ_FLG type="CHAR"/>
      <PROCESS_INSTANCE type="NUMBER"/>
      <PUBLISH_RULE_ID type="CHAR"/>
      <MSGNODENAME type="CHAR"/>
    </PSCAMA>
  </FieldTypes>
  <MsgData>
    <Transaction>
      <PSOPRDEFN class="R">
        <OPRID IsChanged="Y">SF0019</OPRID>
        <USERIDALIAS></USERIDALIAS>
        <OPRDEFNDESC></OPRDEFNDESC>
        <EMPLID IsChanged="Y">SF0011</EMPLID>
        <EMAILID></EMAILID>
        <OPRCLASS></OPRCLASS>
        <ROWSECCLASS></ROWSECCLASS>
        <LANGUAGE_CD IsChanged="Y">ENG</LANGUAGE_CD>
        <MULTILANG>0</MULTILANG>
        <CURRENCY_CD></CURRENCY_CD>
        <ACCTLOCK>0</ACCTLOCK>
        <PRCSPRFLCLS></PRCSPRFLCLS>
        <DEFAULTNAVHP></DEFAULTNAVHP>
        <EXPENT>0</EXPENT>
        <OPRTYPE>0</OPRTYPE>
        <LASTSIGNONDTTM IsChanged="Y">2019-02-21T19:35:38.315896-0800</LASTSIGNONDTTM>
        <LASTUPDDTTM IsChanged="Y">2019-02-21T17:49:09.000000-0800</LASTUPDDTTM>
        <LASTUPDOPRID IsChanged="Y">PS</LASTUPDOPRID>
        <PTALLOWSWITCHUSER>0</PTALLOWSWITCHUSER>
        <PSROLEUSER class="R">
          <ROLEUSER IsChanged="Y">SF0019</ROLEUSER>
          <ROLENAME IsChanged="Y">CS - Student</ROLENAME>
          <DYNAMIC_SW IsChanged="Y">N</DYNAMIC_SW>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">CS - Student</ROLENAME>
            <CLASSID IsChanged="Y">HCCPCSSA1010</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">CS - Student</ROLENAME>
            <CLASSID IsChanged="Y">HCCPCSSA1060</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">CS - Student</ROLENAME>
            <CLASSID IsChanged="Y">HCCPCSSA1070</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">CS - Student</ROLENAME>
            <CLASSID IsChanged="Y">HCCPCSSA6020</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">CS - Student</ROLENAME>
            <CLASSID IsChanged="Y">HCCPCSSA6040</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">CS - Student</ROLENAME>
            <CLASSID IsChanged="Y">HCCPCSSA6050</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">CS - Student</ROLENAME>
            <CLASSID IsChanged="Y">HCCPCSSA6051</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">CS - Student</ROLENAME>
            <CLASSID IsChanged="Y">HCCPCSSA6060</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">CS - Student</ROLENAME>
            <CLASSID IsChanged="Y">HCCPCSSA6061</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">CS - Student</ROLENAME>
            <CLASSID IsChanged="Y">HCCPCSSA6070</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">CS - Student</ROLENAME>
            <CLASSID IsChanged="Y">HCCPCSSA6080</CLASSID>
          </PSROLECLASS>
        </PSROLEUSER>
        <PSROLEUSER class="R">
          <ROLEUSER IsChanged="Y">SF0019</ROLEUSER>
          <ROLENAME IsChanged="Y">EOPP_USER</ROLENAME>
          <DYNAMIC_SW IsChanged="Y">N</DYNAMIC_SW>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">EOPP_USER</ROLENAME>
            <CLASSID IsChanged="Y">EOPP0000</CLASSID>
          </PSROLECLASS>
        </PSROLEUSER>
        <PSROLEUSER class="R">
          <ROLEUSER IsChanged="Y">SF0019</ROLEUSER>
          <ROLENAME IsChanged="Y">PeopleSoft User</ROLENAME>
          <DYNAMIC_SW IsChanged="Y">N</DYNAMIC_SW>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">PeopleSoft User</ROLENAME>
            <CLASSID IsChanged="Y">PTPT1000</CLASSID>
          </PSROLECLASS>
        </PSROLEUSER>
        <PSROLEUSER class="R">
          <ROLEUSER IsChanged="Y">SF0019</ROLEUSER>
          <ROLENAME IsChanged="Y">Standard Non-Page Permissions</ROLENAME>
          <DYNAMIC_SW IsChanged="Y">N</DYNAMIC_SW>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">Standard Non-Page Permissions</ROLENAME>
            <CLASSID IsChanged="Y">HCSPCMPINT</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">Standard Non-Page Permissions</ROLENAME>
            <CLASSID IsChanged="Y">HCSPMSGCHN</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">Standard Non-Page Permissions</ROLENAME>
            <CLASSID IsChanged="Y">HCSPMSSCHG</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">Standard Non-Page Permissions</ROLENAME>
            <CLASSID IsChanged="Y">HCSPPRCS</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">Standard Non-Page Permissions</ROLENAME>
            <CLASSID IsChanged="Y">HCSPSERVICE</CLASSID>
          </PSROLECLASS>
          <PSROLECLASS class="R">
            <ROLENAME IsChanged="Y">Standard Non-Page Permissions</ROLENAME>
            <CLASSID IsChanged="Y">HCSPSIGNON</CLASSID>
          </PSROLECLASS>
        </PSROLEUSER>
        <PSOPRALIAS class="R">
          <OPRID IsChanged="Y">SF0019</OPRID>
          <OPRALIASTYPE IsChanged="Y">EMP</OPRALIASTYPE>
          <OPRALIASVALUE IsChanged="Y">SF0011</OPRALIASVALUE>
          <SETID></SETID>
          <EMPLID IsChanged="Y">SF0011</EMPLID>
          <CUST_ID></CUST_ID>
          <VENDOR_ID></VENDOR_ID>
          <APPLID></APPLID>
          <CONTACT_ID></CONTACT_ID>
          <PERSON_ID></PERSON_ID>
          <EXT_ORG_ID></EXT_ORG_ID>
          <BIDDER_ID></BIDDER_ID>
          <EOTP_PARTNERID>0</EOTP_PARTNERID>
        </PSOPRALIAS>
      </PSOPRDEFN>
      <PSCAMA class="R">
        <LANGUAGE_CD>ENG</LANGUAGE_CD>
        <AUDIT_ACTN></AUDIT_ACTN>
        <BASE_LANGUAGE_CD>ENG</BASE_LANGUAGE_CD>
        <MSG_SEQ_FLG></MSG_SEQ_FLG>
        <PROCESS_INSTANCE>0</PROCESS_INSTANCE>
        <PUBLISH_RULE_ID></PUBLISH_RULE_ID>
        <MSGNODENAME></MSGNODENAME>
      </PSCAMA>
    </Transaction>
  </MsgData>
</CHG_SECURITY_USER>

There are some interesting things to inspect with this.

• We never really dealt with XML at all in our PeopleCode. We only ever touched “rowsets” that were both stand-alone and those attached to the messages.
• The root node is CHG_SECURITY_USER and that matches our message name.
• There is an included meta-data section located under /CHG_SECURITY_USER/FieldTypes. That can be used to parse the node names and pull out the data types since XML really only sends text.
• The actual data is nested under /CHG_SECURITY_USER/MsgData/Transaction
• A record object is designated with a class="R" attribute for example <PSOPRDEFN class="R">. When we allow an external system to send in values into a message that is backed by a rowset object, this attribute is required for PeopleSoft to recognize this as a record object.
• You should also notice that the password related fields we chose not to include (OPERPSWD, PTOPERPSWDV2, OPERPSWDSALT) are not included.
• We applied an alias on OPRDEFNDESC as well as told the system to wrap the value in a CDATA. That seems to not happen. I tested this code in 8.56 and got the result. I know in the past I have used this so some bug has been introduced here.
• There is /CHG_SECURITY_USER/MsgData/Transaction/PSCAMA element. For the purpose of this operation it serves no purpose.
  • If you are syncing data between PeopleSoft systems this can be important. You can read more about that here.
  • See note below about component rowsets.
  • When using a container messages with a Rowset Part, these are not included.
• Most of the fields with values have an IsChanged="Y" attribute. For our use case, this does not make any sense. This is there for syncing delta data between systems.
  • When using a container messages with a Rowset Part, these are not included.
  • See note below about component rowsets.

This structure is sort of forced on you by the system. You can apply some transforms but I don’t often find my self reaching for that. We will look at how to do that in a later section. When you use a Container Rowset

Component Rowsets and Rowset Messages

In the example above there were two items in the XML that we mentioned that were important when syncing data.

• /CHG_SECURITY_USER/MsgData/Transaction/PSCAMA section.
• IsChanged="Y" Field attributes.

Those were of no use in the example where we were exporting data to a 3rd party system when the system asks for it. In that example above those attributes could cause some confusion. You could probably remove them with an XSLT transform which we will cover in a later section. We will also see in the Container Rowset section that these are stripped away.

Where these attributes become useful are when we need to sync transactional changes between PeopleSoft systems. You may see this in service operations like “DEPT_SYNC” or “USER_PROFILE”. In these cases, we need to push incremental changes from one system to another. If a person deletes a row in a component, the subscribing system needs to have some sort of metadata of exactly what changed, so it can mimic that change. That is where these attributes come in.

When you have a Rowset Message that is the exact same structure as a data entry component, the publication of changes becomes trivial. The PeopleTools framework handles all the setting of the metadata attributes in the message. The subscribing system can inspect the metadata and take appropriate actions.

If you have a high-level situation like the following that is syncing department data.

In the SavePostChange logic you might see something similar to this which would publish the changed data.

Local Message &msgDeptSync =  CreateMessage(Message.DEPT_SYNC);
&msgDeptSync.CopyRowsetDelta(GetLevel0());
&msgDeptSync.publish();

There is a lot of “magic” happening in the CopyRowsetDelta method which marks up the PSCAMA records and the IsChanged attributes base on what actually happened in the component. The developer did not have to keep track of all of those changes.