E-SES: PTSEARCHREPORTS Returns No Results (Doc ID 1993756.1)

Last updated on FEBRUARY 20, 2017

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.53 to 8.54 [Release 8.4]
Information in this document applies to any platform.

Symptoms

We have implemented PTSEARCHREPORTS search definition in our PeopleTools 8.54.08 environment.  However, no rows are returned when searching. If we look at the definition, we see it uses the PTRS_CONQRY connected query.  The parent query in that is PTRS_SRCH_QRY.  

The SQL defined for that is as follows:
SELECT DISTINCT A.CONTENT_DESCR, A.DISTNODENAME, A.CONTENTID, A.DISTSTATUS, TO_CHAR(CAST((A.ENDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), TO_CHAR(A.EXPIRATION_DATE,'YYYY-MM-DD'), A.GENPRCSTYPE, A.LOGFILEONLY_FLAG, A.OUTDESTFORMAT, A.OUTPUTDIR, A.PRCSBURSTRPT, A.PRCSINSTANCE, A.PRCSNAME, A.PRCSOUTPUTDIR, A.TRANSFERINSTANCE, A.FILENAME, A.PRCSTYPE, A.PSRF_FOLDER_NAME, TO_CHAR(CAST((A.RQSTDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), TO_CHAR(CAST((A.DTTM_CREATED) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.URL, A.RUNCNTLID, A.OPRID, A.DISTID, A.DBNAME, SUBSTR(TO_CHAR(CAST((TO_CHAR(CAST((A.RQSTDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),1, 4) || '/' || SUBSTR(TO_CHAR(CAST((TO_CHAR(CAST((A.RQSTDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),6, 2) || '/' || SUBSTR(TO_CHAR(CAST((TO_CHAR(CAST((A.RQSTDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),9, 2), '/f/[%A.URL%/%A.CONTENTID%/%A.FILENAME%?ModType=REPORTS&PrcsInstance=%A.PRCSINSTANCE%&ContentId=%A.CONTENTID%]', '/f/[%A.URL%/%A.CONTENTID%/%A.FILENAME%?src=ses]', '/' ||  A.OUTPUTDIR || '/' ||  A.FILENAME, '%URLID:PTRS_FTP_URL%%PTRS_PATHNAME%', TO_CHAR(CAST((C.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')
 FROM PS_PTRS_SRCHQRYVW A, PS_PRCSDEFN B, PSPRCSRQST C
 WHERE ( C.LASTUPDDTTM >= TO_TIMESTAMP(:1,'YYYY-MM-DD-HH24.MI.SS.FF')
    AND A.OPRID = A.DISTID
    AND A.PRCSINSTANCE = C.PRCSINSTANCE
    AND A.PRCSTYPE = B.PRCSTYPE
    AND A.PRCSNAME = B.PRCSNAME
    AND B.TIMESTENMODE = '1')

If we look at the last criteria "B.TIMESTENMODE = '1'", we see we have no rows that would meet that.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms