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

PS Query Migration Validation SQLs

While PeopleSoft PeopleTools Application Designer is the best way to validate query migrations, sometimes a few SQL scripts can be helpful when verifying that queries and associated tree exist in an environment with the necessary nodes.  This SQL can be run in the appropriate SQL editor and was written against an Oracle RDBMS.

Is The Query in the Environment?

SELECT DISTINCT QRYNAME

FROM PSQRYFIELD

WHERE QRYNAME LIKE ’<<QUERYNAME>>%’;

Is Query Tree in Environment?

SELECT TREE_NAME

FROM PSTREENODE

WHERE TREE_NAME =’<<TREENAME>>’

ORDER BY 1;

Is Record in Query Security Tree?

SELECT TREE_NODE

FROM PSTREENODE

WHERE TREE_NAME =’<<TREENAME>>’

AND TREE_NODE LIKE ’%<<RecordName>>%’;

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

How to Add User Identification to a PeopleSoft Query?

In PeopleSoft (PS) query, it is possible to add the user name of the person who ran the query as a column.  To do so create an expression, substring the %Operator global variable, and use the expression as a field.

Example PeopleSoft Query Expression for UserID

SUBSTRING(%Operator,1,30)

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