My Oracle Support Banner

EGL9.2:PeopleSoft Advanced Query Security Sync With Chartfield Security is Resulting in Many Duplicate Rows (Doc ID 2323999.1)

Last updated on NOVEMBER 23, 2020

Applies to:

PeopleSoft Enterprise FIN General Ledger - Version 9.2 to 9.2 [Release 9]
PeopleSoft Enterprise PT PeopleTools - Version 8.55 to 8.55 [Release 8.4]
Information in this document applies to any platform.

Symptoms

Issue:

PeopleSoft Advanced Query Security Sync with Chartfield Security is resulting in many duplicate rows.
Running the query using PSQuery from PS_DISTRIB_LINE, and PS_JRNL_LN tables, for example, the result would show duplicate rows, with total of 27,436 rows.
But in selecting DISTINCT in PSQUERY properties, same query would only result to 19 rows.


Steps to replicate:

1.  Have Chartfield Security (Main Menu > Set Up Financials/Supply Chain>Security > Chartfield Security)
     In Secure CF Options, set Security Method = Role for Operating Unit and Account fields
     Inquiry Views:  User ID View set to cloned from delivered views PS_RJF_SECV_OU_RVW and PS_RJ_SECV_ACC_RVW for Operating Unit and Account respectively.

2.  In Advanced Query Security Management - Security Type tab (Set Up Financials/Supply Chain > Security > Advanced Query Security > Apply Advanced Query Security)
      Set Secure Method to User ID for Field Names Account and Operating Unit.

     In Query Record tab, for Query Records DISTRIB_LINE, JRNL_LN and LEDGER, select Account and Operating Unit, and use for User ID Records, the cloned views PS_RJ_SECV_ACC_RVW and PS_RJF_SECV_OU_RVW respectively

3.  For secured fields Account and Operating Unit (Main Menu > Set Up Financials/Supply Chain > Security > Advanced Query Security > Advanced Query Security Field)
     use delivered, nothing changed.

4. Using PSQuery, results show duplicate rows, but with DISTINCT selected, results show lesser rows.

Replication screenshots attached.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


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