REFRESH SJT_CLASS_ALL APPLICATION ENGINE (SCRTY_CLSUPD) DELETES ROWS FOR SETID
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.
On : 9.1 & 9.2 version, Other
Issue with the refresh SJT_CLASS_ALL application engine (SCRTY_CLSUPD)
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:
WHERE EXISTS (
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.
SJT_CLASS_ALL program should not deleted any SETID related data. It should only refresh the data.
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.
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