PeopleSoft 8.58.03 PUM 35- Activity Guide context data lookup - SQL error
I have setup an activity guide which should prompt for jobcode. The below setup for prompt in the context Data displays a SQL error:
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Executing SQL directly; no cursor. (SQLSTATE 01000) 16954
Failed SQL stmt: SELECT AGC_PROMPT_VALUE, AGC_PROMPT_DESCR FROM (SELECT A.JOBCODE AGC_PROMPT_VALUE, A.DESCR AGC_PROMPT_DESCR FROM PS_JOBCODE_TBL A WHERE A.EFF_STATUS = 'A' AND A.EFFDT=(SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL AA WHERE AA.SETID=A.SETID AND AA.JOBCODE=A.JOBCODE AND AA.EFFDT<={ fn CURDATE() })) ORDER BY 1