REFRESH SJT_CLASS_ALL APPLICATION ENGINE (SCRTY_CLSUPD) DELETES ROWS FOR SETID

(Doc ID 2339849.1)

Last updated on FEBRUARY 15, 2018

Applies to:

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

Symptoms

On : 9.1 & 9.2 version, Other
Issue with the refresh SJT_CLASS_ALL application engine (SCRTY_CLSUPD)

ACTUAL BEHAVIOR
---------------
When the application engine is run to refresh a specific security tree, it deletes all the entries in the SJT_CLASS_ALL table which are associated with the same SetID but does not belong to the department security tree.
Since the run control table specifies the set ID as 10007, the program deletes all the entries associated with the Set ID 10007 in the SJT_CLASS_ALL table but since the run control also specifies the tree name as "DEPT_SECURITY", it inserts the entries which are associated with the set ID 10007 and the tree "DEPT_SECURITY".

The issue here is that any rows other than the ones with the Set ID 10007 and tree "DEPT_SECURITY" should not have been deleted in the first place. The app engine deleted all the rows and then when re-inserting data into the table SJT_CLASS_ALL , only the data associated with the Set ID 10007 and tree "DEPT_SECURITY" are inserted.

The issue is in the delete statement in Section:40Nodeup Step: Step01 Action: SQL in the app engine.

The SQL statement is:

DELETE
  FROM PS_SJT_CLASS_ALL
 WHERE EXISTS (
 SELECT 'X'
  FROM PS_SCRTY_TYPE_TBL TYP
 WHERE TYP.TREE_NAME = %Bind(TREE_NAME)
  AND TYP.ENABLED = 'Y')
  AND SCRTY_KEY1 = %Bind(SETID)

Where tree Name is "DEPT_SECURITY" and setID is 10007. The sql will delete all the rows with the set ID 10007 irrespective of the tree_name.


 

EXPECTED BEHAVIOR
-----------------------
SJT_CLASS_ALL program should not deleted any SETID related data. It should only refresh the data.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Go to Setup HCM>Security>core Row level security
2. Run Refresh SJT_CLASS_ALL app engine with specific run control parameters like a particular SETID
3. System deletes the SETID related data from SJT_CLASS_ALL table.

Changes

 

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