Terminations Analysis (PER019) - SQL Error: "(SQR 3722) Could not set up Cursor." (Doc ID 1907751.1)

Last updated on MAY 10, 2016

Applies to:

PeopleSoft Enterprise HRMS Human Resources - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

When attempting to Run Termination analysis report, the following error occurs.

ERROR
-----------------------
PER019: Start Program PER019 (Terminations Analysis)
FLOW: Get-Establishment-Info
(SQR 5528) ORACLE OCIStmtExecute error 933 in cursor 12:
  ORA-00933: SQL command not properly ended
SQL: SELECT CC.ESTABID, AA.EMPLID, PDEE.SEX, AA.BIRTHDATE, BB.HIRE_DT,
  CC.EFFDT FROM PS_PERSON AA, PS_EMPLOYMENT BB, PS_JOB CC, PS_ESTAB_TBL
  EE, PS_ESTAB_TBL_USA EE1, PS_PERS_DATA_EFFDT PDEE, PS_PERS_SRCH_GBL GBLT
  WHERE GBLT.OPRID=:1 AND AA.EMPLID=GBLT.EMPLID AND
  BB.EMPL_RCD=GBLT.EMPL_RCD AND AA.EMPLID = BB.EMPLID AND CC.PER_ORG =
  'EMP' AND CC.EMPL_STATUS NOT IN ('X','V') AND BB.EMPLID = CC.EMPLID AND
  PDEE.EMPLID = AA.EMPLID AND CC.ESTABID = EE.ESTABID AND BB.EMPL_RCD =
  CC.EMPL_RCD AND CC.ACTION IN ('TER','TWP','TWB') AND CC.JOB_INDICATOR =
  'P' And PDEE.SEX <> 'U' AND CC.EFFDT = (SELECT MAX(CC3.EFFDT) FROM
  PS_JOB CC3 WHERE CC3.EMPLID = CC.EMPLID AND CC3.EMPL_RCD = CC.EMPL_RCD
  AND CC3.EFFDT <= '22-MAY-2014') AND CC.EFFSEQ = (SELECT MAX(CC2.EFFSEQ)
  FROM PS_JOB CC2 WHERE CC2.EMPLID = CC.EMPLID AND CC2.EMPL_RCD =
  CC.EMPL_RCD AND CC2.EFFDT = CC.EFFDT) AND PDEE.EFFDT = (SELECT
  MAX(PDEE2.EFFDT) FROM PS_PERS_DATA_EFFDT PDEE2 WHERE PDEE2.EMPLID =
  PDEE.EMPLID AND PDEE2.EFFDT <= '22-MAY-2014') AND E22.EFFDT <=
  '22-MAY-2014') AND CC.ESTABID = 'KU001' AND EE.ESTABID=EE1.ESTABID AND
  EE.EFFDT=EE1.EFFDT Order by CC.ESTABID,AA.EMPLID,CC.EFFDT

Error on line 585:
  (SQR 3722) Could not set up cursor.

SQR for PeopleSoft: Program Aborting.

STEPS TO REPLICATE

The issue can be reproduced at will with the following steps:
1. Log into HR Demo environment. Navigate to Terminations Analysis. Select ‘Single Establishment’ and ‘As of Date’. Then click on Next:

2. Select Establishment ID KU001 and an As of Date of ‘Today’. Click Run and OK:

3. Check the Process Monitor. The SQR runs to an error. Here is the log file:

PER019: Start Program PER019 (Terminations Analysis)
FLOW: Get-Establishment-Info
(SQR 5528) ORACLE OCIStmtExecute error 933 in cursor 12:
  ORA-00933: SQL command not properly ended
SQL: SELECT CC.ESTABID, AA.EMPLID, PDEE.SEX, AA.BIRTHDATE, BB.HIRE_DT,
  CC.EFFDT FROM PS_PERSON AA, PS_EMPLOYMENT BB, PS_JOB CC, PS_ESTAB_TBL
  EE, PS_ESTAB_TBL_USA EE1, PS_PERS_DATA_EFFDT PDEE, PS_PERS_SRCH_GBL GBLT
  WHERE GBLT.OPRID=:1 AND AA.EMPLID=GBLT.EMPLID AND
  BB.EMPL_RCD=GBLT.EMPL_RCD AND AA.EMPLID = BB.EMPLID AND CC.PER_ORG =
  'EMP' AND CC.EMPL_STATUS NOT IN ('X','V') AND BB.EMPLID = CC.EMPLID AND
  PDEE.EMPLID = AA.EMPLID AND CC.ESTABID = EE.ESTABID AND BB.EMPL_RCD =
  CC.EMPL_RCD AND CC.ACTION IN ('TER','TWP','TWB') AND CC.JOB_INDICATOR =
  'P' And PDEE.SEX <> 'U' AND CC.EFFDT = (SELECT MAX(CC3.EFFDT) FROM
  PS_JOB CC3 WHERE CC3.EMPLID = CC.EMPLID AND CC3.EMPL_RCD = CC.EMPL_RCD
  AND CC3.EFFDT <= '21-MAY-2014') AND CC.EFFSEQ = (SELECT MAX(CC2.EFFSEQ)
  FROM PS_JOB CC2 WHERE CC2.EMPLID = CC.EMPLID AND CC2.EMPL_RCD =
  CC.EMPL_RCD AND CC2.EFFDT = CC.EFFDT) AND PDEE.EFFDT = (SELECT
  MAX(PDEE2.EFFDT) FROM PS_PERS_DATA_EFFDT PDEE2 WHERE PDEE2.EMPLID =
  PDEE.EMPLID AND PDEE2.EFFDT <= '21-MAY-2014') AND E22.EFFDT <=
  '21-MAY-2014') AND CC.ESTABID = 'KU001' AND EE.ESTABID=EE1.ESTABID AND
  EE.EFFDT=EE1.EFFDT Order by CC.ESTABID,AA.EMPLID,CC.EFFDT

Error on line 585:
  (SQR 3722) Could not set up cursor.

SQR for PeopleSoft: Program Aborting.

4. Line 696 of the SQR, shown below, is causing the error. This is the second select statement in the Retrieve-Rows procedure. The SQL uses an alias of “E22” but there is no record in the SQL that corresponds to this alias.


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