Sometimes it is nice to just have a quick reference to the Record Types from the Record Definitions, as here is a quick reference SQL code:

SELECT R.RECNAME AS RECORD_NAME,
( CASE
WHEN R.RECTYPE = 0 THEN ‘Table’
WHEN R.RECTYPE = 1 THEN ‘View’
WHEN R.RECTYPE = 2 THEN ‘Derived’
WHEN R.RECTYPE = 3 THEN ‘Sub Record’
WHEN R.RECTYPE = 5 THEN ‘Dynamic View’
WHEN R.RECTYPE = 6 THEN ‘Query View’
WHEN R.RECTYPE = 7 THEN ‘Temporary Table’
ELSE ‘Unknown’
END )   AS RECORD_TYPE
FROM   PSRECDEFN R

Entity Relationship Diagrams

PeopleSoft Enterprise uses entity-relationship models in the design of the various applications. Entity relations diagrams (ERD’s) capture the relationships between, details of, and constraints imposed on the data defined in these models. ERD’s provide a visual representation of the database scheme to aid customers in the understanding and customization to the PeopleSoft Enterprise application.  These diagrams use to fairly difficult to get but now you can get them relatively easily through MOS Document Id: 1051533.1.

The new ERD’s actually are integrated with the application and after applying them to the environment and doing a couple security items you will find a new menu item: ‘Data Models’.  The older models have a structure that allows you to select various areas and then it will open up a PDF document that shows you the relationship.

It should be noted that not every relationship is available via the delivered ERDs, but for the most part it is an excellent starting point for people new to PeopleSoft to get orientated to the PeopleSoft Application Data Structure.

A quick shot out to my friend Duncan at PeopleSoft Tipster for his post years ago on the same topic: PeopleSoft Entity Relationship Diagrams (ERDs)

ERDs are available for:

HCM:   9.2 Note: 1559851.1, 9.1 Note: 968850.1, 9.0 Note: 979328.1, 8.9 Note: 981781.1, 8.8 Note: 989720.1

ELM:    9.2 Note: 1566244.1, 9.1 Note: 989317.1, 9.0 Note: 989318.1, 8.8 Note: 1050817.1

CS:       9.0/8.9 Note: 1053084.1

CRM:   9.2 Note: 2072285.1, 9.1 Note: 986733.1, 9.0 Note: 978853.1, 8.9 Note: 981780.1

FMS:    9.2 Note: 1547382.1, 9.1 Note: 1074856.1, 9.0 Note: 961655.1, 8.9 Note: 979359.1, 8.8 Note: 989673.1

SCM/SRM:   9.2 Note: 1547384.1, 9.1  Note: 1074949.1, 9.0 Note: 975752.1, 8.9 Note: 981711.1, 8.8 Note: 989690.1

EPM:   9.1/9.0/8.9 Note: 985535.1

Portal:  9.1 Note:1061022.1, 8.9 Note: 988728.1

PeopleSoft Object Owner: (OBJECTOWNERID)

Many years ago PeopleSoft introduced the object owner id which helps identify pretty much every PeopleSoft object (records, pages, content references, etc….) into where that object belongs.

This past week while working on a security matrix, the matrix needed to be broken down by module.  While with a little SQL magic and a little help from Excel and its filters the task was made significantly easier. In order to get a listing of the object owner ids within the system you can use look them up in the PSXLATITEM table.

SELECT * FROM PSXLATITEM WHERE FIELDNAME = ‘OBJECTOWNERID’ ORDER BY FIELDVALUE

If you want a specific application, Finance Objects have objectownerid starting with “F”, Human Resources “H”, Supply Chain “D”, Campus Solutions “S”, PeopleTools = “PPT”.  I also found some modules had multiple objectownerid values.

This handy bit of SQL courtesy of my friend Issam came in really handy, I honestly had never worked with the “WITH” function.  This code is specific to SQL Server 2014, but with a tweak or two it will work in Oracle 12 without an issue.  I am sure it will run in earlier versions, but as I have limited experience with the “WITH” function, it was only tested with MSSQL 2014 and Oracle 12c.

WITH PR (PORTAL_NAME, PORTAL_PRODUCT, PORTAL_SEQ_NUM, OBJECTOWNERID, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, MYPATH) AS (
SELECT P.PORTAL_NAME, P.PORTAL_PRODUCT, P.PORTAL_SEQ_NUM, P.OBJECTOWNERID, P.PORTAL_OBJNAME, P.PORTAL_LABEL, P.PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, cast(P.PORTAL_LABEL as varchar(4000)) AS MYPATH FROM PSPRSMDEFN P
WHERE P.PORTAL_LABEL = ‘Root’
AND P.PORTAL_NAME = ‘EMPLOYEE’
UNION ALL
SELECT P_ONE.PORTAL_NAME, P_ONE.PORTAL_PRODUCT, P_ONE.PORTAL_SEQ_NUM, P_ONE.OBJECTOWNERID, P_ONE.PORTAL_OBJNAME, P_ONE.PORTAL_LABEL, P_ONE.PORTAL_REFTYPE, P_ONE.PORTAL_URI_SEG1, P_ONE.PORTAL_URI_SEG2, cast( (MYPATH + ‘ –> ‘ + P_ONE.PORTAL_LABEL) as varchar(4000)) AS MYPATH FROM PR 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_LABEL <> ‘Root’ AND P_ONE.PORTAL_NAME = ‘EMPLOYEE’ )

SELECT PORTAL_NAME, PORTAL_PRODUCT, PORTAL_SEQ_NUM, OBJECTOWNERID, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, MYPATH FROM PR;

GO

You can use the PeopleSoft classes to encrypt and decrypt values.

To Encrypt a value:

Local JavaObject &SPCipherIn = CreateJavaObject("com.peoplesoft.pt.integrationgateway.common.EncryptPassword");
&vte = "valuetoencrypt";
&encryptedValue = &SPCipherIn.encryptPassword(&vte);


To Decrypt a value:

Local JavaObject &SPCipherOut = CreateJavaObject("psft.pt8.pshttp.PSCipher");
&decryptedValue = &SPCipherOut.decodePassword(&encryptedValue);


Here is a quick way to get started when trying to send a message to the HttpListeningConnector within PeopleSoft:

Basic URL: http://myserver.com/PSIGW/HttpListeningConnector
Advanced URL: http://myserver.com/PSIGW/HttpListeningConnector?&Operation={MyServiceOperation.Version#}&From={MyExternalNodeName}

Next you need to generate a message to pass to this Target Connector. Use a simple SOAP envelope and wrap you message into it:

<?xml version="1.0"?>
<soapenv:Envelope xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:wsa="http://schemas.xmlsoap.org/ws/2003/03/addressing/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance/">
<soapenv:Header xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<wsse:Security soap:mustUnderstand="1"
xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
<wsse:UsernameToken>
<wsse:Username>{ExternalUserId}</wsse:Username>
<wsse:Password>{ExternalUserIdPassword}</wsse:Password>
</wsse:UsernameToken>
</wsse:Security>
</soapenv:Header>
<soapenv:Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<{MESSAGE_NAME}>
<FieldTypes>
<{MESSAGE_RECORDNAME} class="R">
<{FIELD1} type="CHAR"/>
<{FIELD2} type="CHAR"/>
</{MESSAGE_RECORDNAME}>
<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>
<{MESSAGE_RECORDNAME} class="R">
<{FIELD1}>SomeData1</{FIELD1}>
<{FIELD2}>SomeData2</{FIELD2}>
</{MESSAGE_RECORDNAME}>
<PSCAMA class="R">
<LANGUAGE_CD IsChanged="Y"/>
<AUDIT_ACTN>A</AUDIT_ACTN>
<BASE_LANGUAGE_CD IsChanged="Y"/>
<MSG_SEQ_FLG/>
<PROCESS_INSTANCE>0</PROCESS_INSTANCE>
<PUBLISH_RULE_ID/>
<MSGNODENAME/>
</PSCAMA>
</Transaction>
</MsgData>
</{MESSAGE_NAME}>
</soapenv:Body>
</soapenv:Envelope>