My Oracle Support Banner

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

Last updated on MAY 09, 2018

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

To view full details, 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 a vibrant support community of peers and Oracle experts.