EEX 9.1+: AE Program EX_EE_ORG_LD.EEORGINS.UPDMNDT SQL Updates PS_EMPLOYMENT.TERMINATION_DT Each Time Is Run (Doc ID 1964323.1)

Last updated on JANUARY 03, 2023

Applies to:

Symptoms

ISSUE:

It has been detected that the delivered Application Engine Program EX_EE_ORG_LD (Load Organizational Data), called from the 'Employee Load Data' process (EX_EE_UPDATE), has within Section EEORGINS and Step UPDMNDT SQL a codeline that is targeting to constantly update Record Field PS_EMPLOYMENT.TERMINATION_DT with the current date of the day the process is run for those Employees who have been terminated in the system. This seems to be regardless of the fact if PeopleSoft Enterprise HRMS is being used to create/update Employee Personal/Employment/Banking information, or such data is directly created only in PeopleSoft Enterprise FSCM.

  - Application Engine Program EX_EE_ORG_LD, Section EEORGINS, Step UPDMNDT SQL

The delivered codeline in the above object looks like below:

UPDATE PS_EMPLOYMENT
SET TERMINATION_DT = %CurrentDateIn
WHERE EMPLID IN (
SELECT A.EMPLID
FROM PS_JOB A
, PS_EX_EE_ORG_DTA B
WHERE A.EMPLID = B.EMPLID
AND A.EFFDT = (
SELECT MAX(A1.EFFDT)
FROM PS_JOB A1
WHERE a1.emplid = a.emplid
AND a1.empl_rcd = a.empl_rcd
AND a1.effseq = a.effseq
AND A1.EFFDT <= %CurrentDateIn)
AND a.empl_rcd = (
SELECT MAX(A2.EFFSEQ)
FROM PS_JOB A2
WHERE A2.EMPLID = A.EMPLID
AND A2.EMPL_RCD = A.EMPL_RCD
AND A2.EFFDT = A.EFFDT)
AND a.empl_status NOT IN ('A', 'L', 'P', 'W', 'S'))

REPLICATION STEPS:

  - Perform all the necessary configuration between HRMS and FSCM environments to allow PERSON_BASIC_SYNC and WORKFORCE_SYNC integration
  - Log into the HRMS Online Application as User ID PS
  - Navigate to: Workforce Administration > Personal Information > Add a Person
  - Define a Person ID of GCS0001, and click on the 'Add the Person' link
  - Fill in all the relevant personal and job related information, with an Effective date of January 1st 2010
  - Save all the changes, and allow interface to FSCM systems
  - Log into the FSCM Online Application as User ID VP1
  - Navigate to: Travel and Expenses > Manage Employee Information > Load Employee Data
  - Create a new Run Control ID, select all 4 available options, and launch to completion the EX_EE_UPDATE Application Engine Program
  - Query PS_EX_EE_ORG_DTL Record for Employee ID GCS0001, and confirm that one row with EMPL_RCD = 0 has the flag DFLT_EE_PROF_FLG = Y
  - Navigate to: Travel and Expenses > Manage Security > Authorize Expense Users
  - Open the newly created Employee ID GCS0001
  - Define User ID VP1, and save the changes
  - Navigate to: Set Up Financials/Supply Chain > Business Unit Related > Expenses > Expenses Definition
  - Open Business Unit US001
  - At the Business Unit 2 tab, set the Grace Period of Time Reports and Expense Reports to 90 days
  - Log into the HRMS Online Application as User ID PS
  - Log into the HRMS Online Application as User ID PS
  - Navigate to: Workforce Administration > Job Information > Job Data
  - Open the Employee ID GCS0001 with Job Record Number 0 instance
  - Click on the + icon sign to add a new effective dated row
  - Set the following values at this stage:
       - Effective Date = March 31st 2014
       - Action = Termination
       - Reason = Elimination Of Position
  - Save all the changes, and allow interface to FSCM systems
  - Log into the FSCM Online Application as User ID VP1
  - Navigate to: Travel and Expenses > Manage Employee Information > Load Employee Data
  - Open the Run Control ID previously created, select all 4 available options, and launch to completion the EX_EE_UPDATE Application Engine Program
  - Query PS_EMPLOYMENT Record for Employee ID GCS0001, and confirm that on Field TERMINATION_DT the value is the Current Date when the EX_EE_UPDATE Application Engine Program, instead of March 31st 2014
  - Navigate to: Employee Self-Service > Travel and Expense Center > Expense Report > Create/Modify
  - Open the look-up view of Employee ID, and find value GCS0001
  - Navigate to: Employee Self-Service > Travel and Expense Center > Time Report > Create/Modify
  - Open the look-up view of Employee ID, and find value GCS0001

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

ACTUAL RESULT:

This behavior is causing below identified problems:

 a) Creates an inconsistency between HRMS and FSCM systems on Record Field PS_EMPLOYMENT.TERMINATION_DT, which should always be in-sync if Employee data comes from PeopleSoft Enterprise HRMS
 b) Nullifies the functionality behind the Grace Period feature, as SQL View PS_EX_EE_AUTH_1_VW uses Record Field PS_EMPLOYMENT.TERMINATION_DT to determine if a Terminated Employee is to be eligible for Expense Report and Time Report transactions creation. Because the Termination Date gets set to Current Date each time the EX_EE_ORG_LD Application Engine Program is run, the Employee will always fall within the Grace Period. Authorized Users could create Expense transactions on behalf of Employees that were terminated years ago.
 c) The EX_EE_ORG_LD Application Engine Program recalculates the value stored within Record Field PS_EMPLOYMENT.TERMINATION_DT for all Terminated Employees each time it is run. Over time, thousands upon thousands of Terminated Employes will be existing in the system, all of them re-processed again and again, which will ultimately create performance problems.

EXPECTED BEHAVIOR:

If PeopleSoft Enterprise HRMS systems are not being used, and all Employee data gets created directly in FSCM systems, the way to perform Job related changes is via the 'Update Employee Data' component (RS_CM_PERSONAL) from the Resource Management module (navigation here below). This component RS_CM_PERSONAL is enabled to all customers, even if the Resource Management (RS) Module is not licensed. There, the Users can add effective dated rows, with status of Terminated/Inactive, and that Effective Date should be the value used in PS_EMPLOYMENT.TERMINATION_DT Record Field.

 - Resource Management > Establish Resources > Update Employee Data > Employment Job tab (Page RS_CM_EMPL_JOB)

On the contrary, if PeopleSoft Enterprise HRMS systems are being used to create/update the Employees' Personal/Employment/Banking data, to later on integrate that information with the PeopleSoft Enterprise FSCM databases, these should always be in-sync. The value within PS_EMPLOYMENT.TERMINATION_DT Record Field coming from HRMS should always be respected in FSCM, and never overridden automatically by any other process. 

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.