Unable to Create View PS_PC_PARCHLD_ACT. Receiving Error - "SQL Error. Error Position: 0 Return: 8601 - [Microsoft][SQL Server Native Client 10.0][SQL Server]'SUBSTR' is not a recognized built-in function name." (Doc ID 2080599.1)

Last updated on NOVEMBER 24, 2015

Applies to:

PeopleSoft Enterprise FIN Project Costing - Version 9.1 to 9.1 [Release 9]
Information in this document applies to any platform.

Symptoms

On : 9.1 version, Other, MicroSoft SQL Server

When attempting to apply 9.1 ESA bundle #28, the following "Run Create Views Script" fails
the following error occurs.

ERROR
-----------------------
 Message Set Number: 200
 Message Number: 0
 Message Reason: File: Data MoverSQL error. Stmt #: 0 Error Position: 0 Return: 8601 - [Microsoft][SQL Server Native Client 10.0][SQL Server]'SUBSTR' is not a recognized built-in function name.
[Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared. (SQLSTATE 37000) 8180
Failed SQL stmt:CREATE VIEW PS_PC_PARCHLD_ACT (BUSINESS_UNIT, PROJECT_ID, ACTIVITY_ID, LEVEL_NUM, CHILD_ACTIVITY_ID, CHILD_LEVEL_NUM, WBS_ID) AS SELECT A.BUSINESS_UNIT , A.PROJECT_ID , A.ACTIVITY_ID , A.LEVEL (200,0)
SQL Error. Error Position: 0 Return: 8601 - [Microsoft][SQL Server Native Client 10.0][SQL Server]'SUBSTR' is not a recognized built-in function name.

[Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared. (SQLSTATE 37000) 8180

CREATE VIEW PS_PC_PARCHLD_ACT (BUSINESS_UNIT, PROJECT_ID, ACTIVITY_ID, LEVEL_NUM, CHILD_ACTIVITY_ID, CHILD_LEVEL_NUM, WBS_ID) AS SELECT A.BUSINESS_UNIT , A.PROJECT_ID , A.ACTIVITY_ID , A.LEVEL_NUM , B.ACTIVITY_ID , B.LEVEL_NUM , B.WBS_ID FROM PS_PROJ_ACTIVITY A , PS_PROJ_ACTIVITY B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.ACTIVITY_ID = B.ACTIVITY_ID AND A.LEVEL_NUM = 1 UNION ALL SELECT A.BUSINESS_UNIT , A.PROJECT_ID , A.ACTIVITY_ID , A.LEVEL_NUM , B.ACTIVITY_ID , B.LEVEL_NUM , B.WBS_ID FROM PS_PROJ_ACTIVITY A , PS_PROJ_ACTIVITY B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.WBS_ID = CASE WHEN LEN(B.WBS_ID) > 1 THEN SUBSTR(B.WBS_ID,1,LEN(B.WBS_ID) - 2) END AND A.LEVEL_NUM = B.LEVEL_NUM - 1


Error: SQL execute error for CREATE VIEW PS_PC_PARCHLD_ACT (BUSINESS_UNIT, PROJECT_ID, ACTIVITY_ID, LEVEL_NUM, CHILD_ACTIVITY_ID, CHILD_LEVEL_NUM, WBS_ID) AS SELECT A.BUSINESS_UNIT , A.PROJECT_ID , A.ACTIVITY_ID , A.LEVEL_NUM , B.ACTIVITY_ID , B.LEVEL_NUM , B.WBS_ID FROM PS_PROJ_ACTIVITY A , PS_PROJ_ACTIVITY B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.ACTIVITY_ID = B.ACTIVITY_ID AND A.LEVEL_NUM = 1 UNION ALL SELECT A.BUSINESS_UNIT , A.PROJECT_ID , A.ACTIVITY_ID , A.LEVEL_NUM , B.ACTIVITY_ID , B.LEVEL_NUM , B.WBS_ID FROM PS_PROJ_ACTIVITY A , PS_PROJ_ACTIVITY B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.WBS_ID = CASE WHEN LEN(B.WBS_ID) > 1 THEN SUBSTR(B.WBS_ID,1,LEN(B.WBS_ID) - 2) END AND A.LEVEL_NUM = B.LEVEL_NUM - 1


Ended: Wed Nov 18 05:46:28 2015

Unsuccessful completion

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Apply 9.1 ESA bundle #28 and perform steps associated with the bundle application.
2. The Step Run Create Views Script fails with above error message.

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