PS Query Timestamp to Date Logic

Converting a timestamp to date in PeopleSoft Query seems to be a common item, which I have helped others with, to the point that I kept a personal note on the coding logic.  So, I thought I would post this quick note in case it may be useful to someone.  This item would seem to be straight forward, however, PS query tries to help you with the process, so, some experimentation is usually involved to get the code to out just right.

Here is a sample expression I use for the timestamp to date conversion in PS Query.

  • Create an expression with an Expression Type of “Number
  • Set Length to 10
  • Sample code: TO_DATE(SUBSTR(<<RecordAlias.TimestampFieldName>>,1,10))
  • PS Query interpretation: TO_DATE(SUBSTR(TO_CHAR(CAST((<<RecordAlias.TimestampFieldName>>) AS TIMESTAMP),’YYYY-MM-DD-HH24.MI.SS.FF’),1,10))

Related References

How to Verify PeopleSoft Record Build in Oracle

A frequent problem during migrations of PeopleSoft record definitions is the omission of a record build, even though the record exists in the PeopleTools project Meta Data.  An easily way to solve this issue is to run a SQL script to test the new record exist and/or that altered record field(s) exist.  A simple count script can provide the necessary answers and save lost project time and reduce testing defect research and resolution activities up front.  Here a couple of quick samples, which can be assembled into a script and run in Oracle SQL Developer as a script.

Verify that a new PeopleSoft record exists in Oracle

SELECT COUNT(*) AS CNT_<<RecordName>>

FROM PS_<<RecordName>>;

Verify that a new field exists on an existing PeopleSoft record in Oracle

SELECT COUNT(<<FieldName>>) As CNT_<<RecordName_FieldName>>

FROM PS_<<RecordName>>;

Note: This SQL can also, be used to see if a field was dropped, only it should produce an error for the removed field.

Related References

PeopleSoft XLAT Value Lookup SQL

I’ve had this SQL snippet laying around for a while, so, before I lose it, I thought I would post it for future reference.  Below is SQL to lookup a XLAT values, in case you don’t want to or cannot go through PeopleSoft Application Designer.

XLAT Lookup SQL

SELECT XLAT.FIELDNAME,

XLATFIELDVALUE,

XLAT.XLATLONGNAME

FROM PSXLATITEM XLAT

WHERE XLAT.FIELDNAME = ‘<<FieldName>>’

AND XLAT.EFF_STATUS = ‘A’

AND XLAT.EFFDT = ( SELECT MAX(XLAT1.EFFDT)

FROM PSXLATITEM XLAT1

WHERE  XLATFIELDNAME =XLAT1.FIELDNAME

AND XLATFIELDVALUE = XLAT1.FIELDVALUE)

ODER BY XLAT.FIELDVALUE

Related References