SQL Error Running HRTREE_BLD AE: "Incorrect syntax near the keyword 'AND'." (Doc ID 2081627.1)

Last updated on NOVEMBER 24, 2015

Applies to:

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

Symptoms

SQL Error running HRTREE_BLD AE

When running the HRTREE_BLD application engine we are getting a SQL error. RPAD and LPAD are not supported in SQL Server.

ERROR

SQL Error 1703

[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'AND'.
[Microsoft][SQL Server Native Client 11.0][SQL Server]
Failed SQL stmt: INSERT INTO PS_HRCD_JOB_TREE (PROCESS_INSTANCE , EMPLID , EMPL_RCD , TREE_NODE , SEQUENCENO , LASTUPDOPRID , LASTUPDDTTM) SELECT 508632 , J.EMPLID , J.EMPL_RCD , RPAD(rpad( J.EMPLID , 11 ,'X')||LPAD(J.EMPL_RCD ,3 ,'0') ,20 ,'0') , 0 , 'SMEERS' , GETDATE() FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EFFDT = ( SELECT MAX (C.EFFDT) FROM PS_JOB C WHERE C.EMPLID = J.EMPLID AND C.EMPL_RCD = J.EMPL_RCD AND C.EFFDT <= '2015-10-09') AND J.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE D.EMPLID = J.EMPLID AND D.EMPL_RCD = J.EMPL_RCD AND D.EFFDT = J.EFFDT) AND NOT EXISTS ( SELECT 'X' FROM PS_HRCD_JOB_TREE T WHERE J.EMPLID = T.EMPLID AND J.EMPL_RCD = T.EMPL_RCD)

Process 508632 ABENDED at Step HR_TREEBLD_P.10BldSrc.Step010 (SQL) -- RC = 8601 (108,524)


STEPS

The issue can be reproduced at will with the following steps:

1. Log into PIA
2. Navigate to Main Menu > Set Up HCM > Common Definitions > Org Chart Viewer > Tree Builder Run Control
3. Run the Process HR_TREEBLD


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