EEX 8.9+: Fatal SQL Error When Trying To Open Expenses Profile Of An Employee With German Royal Name (Doc ID 1536792.1)

Last updated on JUNE 08, 2017

Applies to:

PeopleSoft Enterprise FIN Expenses - Version 8.9 to 9.2 [Release 8.9 to 9]
Information in this document applies to any platform.

Symptoms

ISSUE:

In PeopleSoft Enterprise HRMS systems, the delivered codeline has accommodated country-specific needs for Germany in order to store Royal and Nobility Titles related to Employees Names. Such data is to be stored in Fields NAME_ROYAL_PREFIX and NAME_ROYAL_SUFFIX. When an Employee gets these Fields populated, their contained values get interfaced into the FSCM systems via Integration Broker Message PERSON_BASIC_SYNC, and impact the Records PS_PERSONAL_DATA and PS_PERS_DATA_EFFDT. Should an Employee have those 2 Fields populated with any value, and have the Country Code of his/her Home/Mailing Address set to Germany ('DEU'), the system will trigger a Fatal SQL Error when trying to open his/her Expense Profile.

This is due to the fact that delivered codeline in Record Field FUNCLIB_NAME.PERSON_NAME.FieldFormula executes the Function DEUEV_NAME when the Country Code is Germany ('DEU'). Such Function is stored within Record Field FUNCLIB_HR_GER.NAME.FieldFormula, where its codeline makes reference to Records PS_NM_ROYPREF_GER and PS_NM_ROYSUFF_GER, but neither of them exist in the FSCM Application Designer. The delivered SQL Objects HR_PERSON_NAME_1_SEL, and HR_PERSON_NAME_2_SEL are the ones trying to fetch the Person Name details from PS_PERS_DATA_EFFDT and PS_PERSONAL_DATA respectively.

REPLICATION STEPS:

  - Log into the FSCM Online Application as User ID VP1
  - Navigate to: Travel and Expenses > Manage Employee Information > Update Profile
  - Open the Expenses Profile from existing Employee ID KU0022
  - In the Employee Data tab, make sure to change the Country field to 'DEU' (Germany) in the sections of Home and Mailing Address
  - Complete the rest of Address fields such as City, Post Code, etc... Finally, save the changes
  - In order to simulate the issue, by means of an SQL Tool, execute and commit the below SQL Update statements:
       UPDATE PS_PERSONAL_DATA SET NAME_ROYAL_PREFIX = 'Von', NAME_ROYAL_SUFFIX = 'Graf' WHERE EMPLID = 'KU0022'
       UPDATE PS_PERS_DATA_EFFDT SET NAME_ROYAL_PREFIX = 'Von', NAME_ROYAL_SUFFIX = 'Graf' WHERE EMPLID = 'KU0022'
  - Once completed, make sure that the Fields NAME_ROYAL_PREFIX, and NAME_ROYAL_SUFFIX have been properly populated in both Records PS_PERSONAL_DATA and PS_PERS_DATA_EFFDT
  - Navigate to: Travel and Expenses > Manage Employee Information > Update Profile
  - Open the Expenses Profile from existing Employee ID KU0022
  - The system throws an Error Message at this stage when trying to load the Expenses Profile of Employee ID KU0022

To gather more information concerning this scenario and its related problem, refer to the available Replication Steps Word Document containing the complete configuration and the replication steps necessary to reproduce the issue.

ERROR MESSAGE:

  " A fatal PeopleCode SQL error occurred. Please consult your system log for details.
    SQL error. Function: SQLExec
       Error Position: 18
       Return: 942 - ORA-00942 : table or view does not exist
       Statement: select descr from PS_NM_ROYPREF_GER WHERE NM_ROYAL_PREF = :1 AND EFF_STATUS = 'A'
       Original Statement: sELECT DESCR FROM PS_NM_ROYPREF_GER WHERE NM_ROYAL_PREF = :1 AND EFF_STATUS = 'A'
    SQL error in Exec. (2,280) FUNCLIB_HR_GER.NAME.FieldFormula  Name:DEUEV_NAME  PCPC:431  Statement:4
    Called from:FUNCLIB_NAME.PERSON_NAME.FieldFormula  Name:Format_Display_Name  Statement:22
    Called from:FUNCLIB_NAME.PERSON_NAME.FieldFormula  Name:Get_Person_Name  Statement:116
    Called from:EX_EE_PROFILE2.GBL.PreBuild  Statement:8
    During the execution of SQL, an error occurred in the Exec subroutine. The preceding message should have described the SQL being executed. "

BUSINESS IMPACT:

The Expenses Profile cannot be opened, reviewed, nor maintained in FSCM for those Employees whose Records PS_PERSONAL_DATA and PS_PERS_DATA_EFFDT contain values in Fields NAME_ROYAL_PREFIX, and NAME_ROYAL_SUFFIX, which are used in Germany to accommodate specific naming conventions and nobility titles.

EXPECTED BEHAVIOR:

The system should be able to properly open and display the full Expenses Profile for the German Employees whose Nobility Title fields are being used. If delivered SQL objects make reference to certain Records existing in HRMS codeline, those should also be present in the FSCM systems.

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