E-QR: The Scheduled PS Query Reports Do Not Show The Prompt Values In The XLSX Output File (Doc ID 2025479.1)

Last updated on AUGUST 01, 2017

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.54 and later
Information in this document applies to any platform.
*** Checked For Relevance On 1-Aug-2017 ***

Symptoms

When attempting to schedule PS Query which generates more than 65k the prompt values are not specified in the Excel output file (with xlsx extension).

This is not an issue for the output results generated with less than 65k in the Excel output file (with xls extension).

Steps
-------
The issue can be reproduced at will with the following steps:
1. Login to PIA
2. Navigate to Reporting Tools > Query > Query Manager
3. Create a new PS Query (SB_TEST_XLSX_PARAMETERS)
4. View Query SQL

SELECT A.EMPLID, A.ACTION, B.COMPRATE,
C.STEP, D.DESCR
 FROM ((((PS_JOB A INNER JOIN PS_EMPLMT_SRCH_QRY A1 ON
(A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.OPRID = 'PS' )) LEFT
OUTER JOIN (PS_CURRENT_JOB B INNER JOIN PS_EMPLMT_SRCH_QRY B1 ON (B.EMPLID =
B1.EMPLID AND B.EMPL_RCD = B1.EMPL_RCD AND B1.OPRID = 'PS' )) ON A.EMPLID =
B.EMPLID ) LEFT OUTER JOIN (PS_CURRENT_JOB C INNER JOIN PS_EMPLMT_SRCH_QRY C1
ON (C.EMPLID = C1.EMPLID AND C.EMPL_RCD = C1.EMPL_RCD AND C1.OPRID = 'PS' ))
ON B.EMPLID = C.EMPLID ) LEFT OUTER JOIN PS_JOBCODE_TBL D ON D.JOBCODE =
C.JOBCODE )
 WHERE ( ( D.EFFDT =
  (SELECT MAX(D_ED.EFFDT) FROM
PS_JOBCODE_TBL D_ED
  WHERE D.SETID = D_ED.SETID
  AND
D.JOBCODE = D_ED.JOBCODE
  AND D_ED.EFFDT
AND A.ACTION = :1 ))

5. Navigate to Reporting Tools > Query > Schedule Query
6. Create a new run ctrl id (sb_test)
7. Click on Run button
8. Enter the Prompt value Action = 'HIR'
9. Generate the xlsx output file
10. The output results are more than 65k - '87936' rows are extracted - the prompt value is not specified in the output xlsx file generated
11. Reschedule the PS Query with a prompt value which generates less data than 65 k
12. Enter the Prompt value Action = 'TER' - the prompt value is specified in the output xls file generated

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