PeopleSoft : Application Tables By Module.

Have you ever wondered what we could do with Owner ID property.

We can Owner ID in the following SQL to Get list of PeopleSoft Application tables by Module Name.

select B.XLATLONGNAME  AS "PeopleSoft Module", A.recname AS "PeopleSoft Table" from psrecdefn A,PSXLATITEM B 

where B.fieldname='OBJECTOWNERID' 

and A.OBJECTOWNERID=B.Fieldvalue

and B.effdt = (select max(B1.effdt) from PSXLATITEM B1 where B.fieldname=B1.fieldname and B.fieldvalue=B1.fieldvalue and B1.effdt<=sysdate)

and B.eff_status='A'

order by 1,2;

This would be helpful when starting implementation project/ Collecting tables for creating Data warehouse or data lake.

Use the following SQL to know the List of module names by ObjectownerID property.

SELECT b.Fieldvalue AS "PeopleSoft ObjectOwnerID",b.xlatlongname AS "PeopleSoft Module" FROM PSXLATITEM B

WHERE B.FIELDNAME = 'OBJECTOWNERID'

and B.effdt = (select max(B1.effdt) from PSXLATITEM B1 where B.fieldname=B1.fieldname and B.fieldvalue=B1.fieldvalue and B1.effdt<=sysdate)

and B.eff_status='A'

order by 1;

PeopleSoft: Update user password - Helpful during installation

This is a very simple Data mover script to update a user's password in PeopleSoft
and re-encrypt it.
Useful if you can't access the PIA (e.g. during installation)
but need to update user passwords.

Steps to update the user password.

1) Login into Data mover using Bootstrap mode. (use database access ID and password)
2) Run below script.(in the example, VP2 user password updated and re encrypted).
UPDATE PSOPRDEFN SET PTOPERPSWDV2 = 'VP2', ENCRYPTED = 0, ACCTLOCK = 0
WHERE OPRID = 'VP2';
ENCRYPT_PASSWORD VP2;

PS: This should be done with utmost precautions. Updating userID password
which is part of any configuration can lead to issues in booting application server.

PeopleSoft - Writing Blanks when Field values are Null using PeopleSoft File layout

Ever wondered with an issue of writing a blank space for a field when its original value is null.

Peoplesoft by delivered produces null value when writes data using File layout.

The following code produces output file. But incase if any fields have no value (including blanks) , file layout will trim spaces into Null. This happens by default.

&outputFile = GetFile(&file, "W", %FilePath_Absolute);

&outputFile.SetFileLayout(FileLayout.FILE_LAYOUT);

&rsOutput = &outputFile.CreateRowset();

&rsApplicants.CopyTo(&rsOutput, Record.DATA_STG, Record.FILE_LAYOUT_STG);

&outputFile.WriteRowset(&rsOutput);

If the file needs to be produced with spaces instead of NULL.

use the peoplecode below.

&outputFile = GetFile(&file, "W", %FilePath_Absolute);

&outputFile.SetFileLayout(FileLayout.FILE_LAYOUT);

&rsOutput = &outputFile.CreateRowset();

&rsApplicants.CopyTo(&rsOutput, Record.DATA_STG, Record.FILE_LAYOUT_STG);

&outputFile.UseSpaceForNull = True;   

&outputFile.WriteRowset(&rsOutput);

By default UseSpaceForNull property is False for each file-layout. Setting it to True will make file Layout to write spaces instead of Null Values.

PeopleSoft - Integration Broker - Error Handling and Exception

  PeopleSoft Allows user to handle exceptions using the Peoplecode.  When there are exceptions   arises during sending messages the following code can be deployed to exactly capture the error associated with exception and write to an error report.

      LogoutRespMSG = %IntBroker.SyncRequest(&LogoutMSG);

     If &LogoutRespMSG.ResponseStatus <> %IB_Status_Success Then

      MessageBox(0, "", 0, 0, "Trouble Calling Logout API.please Check integration logs");

     &nMsgNumber = &LogoutRespMSG.IBException.MessageNumber;

     &nMsgSetNumber = &LogoutRespMSG.IBException.MessageSetNumber;

     &exceptString = &LogoutRespMSG.IBException.ToString();

     &UpdxmldocReturn = &LogoutRespMSG.GetXmlDoc();

     MessageBox(0, "", 0, 0, "&exceptString" | &exceptString);

     &logoutxmldocReturn = &LogoutRespMSG.GetXmlDoc();

    &exceptionCode = &logoutxmldocReturn.DocumentElement.GetElementsByTagName("exceptionCode").Get(1).NodeValue;

   &exceptionMessage = &logoutxmldocReturn.DocumentElement.GetElementsByTagName(":exceptionMessage").Get(1).NodeValue;

   MessageBox(0, "", 0, 0, " Logout API exceptionCode:" | &exceptionCode);

   MessageBox(0, "", 0, 0, "Logout API exceptionMessage:" | &exceptionMessage);

 End-If;

This code works when the user exception check box checked at the Service Operation routing definition page. Without this checkbox checked, peoplesoft will fail the integration and logs a generic message.

PeopleSoft - Integration Broker - Override Connector Properties

      
      PeopleSoft Integration Broker Allows to override the connector Properties. 
      The following code example shows how HTTPTARGET connector properties can be set at run time using peoplecode.
    

       &LogoutMSG = CreateMessage(Operation.LOGOUT22824);

      &LogoutMSG.IBInfo.IBConnectorInfo.ConnectorName = "HTTPTARGET";
      &LogoutMSG.IBInfo.IBConnectorInfo.ConnectorClassName = "HttpTargetConnector";
      &b2 = &LogoutMSG.IBInfo.IBConnectorInfo.AddConnectorProperties("Content-Type", "text/xml", %Header);
      &b2 = &LogoutMSG.IBInfo.IBConnectorInfo.AddConnectorProperties("sendUncompressed", "Y", %Header);
      &b2 = &LogoutMSG.IBInfo.IBConnectorInfo.AddConnectorProperties("Method", "POST", %HttpProperty);
      &b2 = &LogoutMSG.IBInfo.IBConnectorInfo.AddConnectorProperties("SOAPUpContent", "Y", %HttpProperty);
/* &serverUrl,  is the End point URL to which message needs to be posted */
      &b2 = &LogoutMSG.IBInfo.IBConnectorInfo.AddConnectorProperties("URL", &serverUrl, %HttpProperty);
  
      &LogoutMSG.IBInfo.ConnectorOverride = True;

     &LogoutMSG.SetXmlDoc(&logout_xmldoc);
     &LogoutRespMSG = %IntBroker.SyncRequest(&LogoutMSG);

     
Points to be considered:

1. Dynamic assignment of Connector properties allows to skip Configuration of Connector Properties at the Service Operation Routing.

Connector Properties in Service Operation Routing