My Oracle Support Banner

EAM: SETID Inconsistencies In DEMO Environments Need Correction (Doc ID 2716257.1)

Last updated on OCTOBER 06, 2020

Applies to:

PeopleSoft Enterprise FIN Asset Management - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.
9.2.37

Symptoms

Some inconsistencies have been observed in DEMO environmet:

1. Entries are missing for some SETCNTRLVALUES (in particular, older ones) - namely, we have an entry for one BU, but that entry doesn't exist for an older BU.
2. Records are in Record Groups, but not in SET_CNTRL_REC (e.g. new records)
3. Records are in SET_CNTRL_REC but the Record Group differs from the Record Group Definition
4. Records are in SET_CNTRL_REC, but SETID is different than the SETID specified on the Record Group
 

See SQLs below to check against most recent DEMO:

This will identify records where the Record is defined within a Record Group, but is on SET_CNTRL_REC with a different Record Group

      SELECT * FROM PS_REC_GROUP_REC A
      WHERE EXISTS (SELECT 'X' FROM PS_SET_CNTRL_REC B
      WHERE A.RECNAME = B.RECNAME
      AND A.REC_GROUP_ID <> B.REC_GROUP_ID)

 

This will identify records where the SETID on the Record Group is not the same as the SETID defined on SET_CNTRL_REC

      SELECT A.*, B.SETID FROM PS_SET_CNTRL_REC A, PS_SET_CNTRL_GROUP B
      WHERE A.REC_GROUP_ID = B.REC_GROUP_ID
      AND A.SETCNTRLVALUE = B.SETCNTRLVALUE
      AND A.SETID <> B.SETID

 

This will identify (based on counts) records that are in SET_CNTRL_REC, but not consistently applied to every single Tableset ID.   This assumes that every SETID should have every record.

      SELECT RECNAME, COUNT(*) FROM PS_SET_CNTRL_REC
      GROUP BY RECNAME
      HAVING COUNT(*) <> (SELECT COUNT(*) FROM PS_SET_CNTRL_TBL)

 

This will provide the total set of values that would appear to be "missing"from PS_SET_CNTRL_REC based on recreating the values based on SET_CNTRL_TBL, SET_CNTRL_GROUP, and REC_GROUP_REC

 

  SELECT DISTINCT A.SETCNTRLVALUE, B.REC_GROUP_ID, C.RECNAME, B.SETID
  FROM PS_SET_CNTRL_TBL A, PS_SET_CNTRL_GROUP B, PS_REC_GROUP_REC C
  WHERE A.SETCNTRLVALUE = B.SETCNTRLVALUE
  AND B.REC_GROUP_ID = C.REC_GROUP_ID
  AND NOT EXISTS (SELECT 'X' FROM PS_SET_CNTRL_REC D
          WHERE A.SETCNTRLVALUE = D.SETCNTRLVALUE
          AND C.RECNAME = D.RECNAME)

Here is a list of inconsistent Records in Record Groups related to AM:


RE_AMNDTYP_SRCH
RE_CLSETYP_SRCH
RE_CRDTMPL_SRCH
RE_EXPCLS_SRCH
RE_LSETYPE_SRCH
RE_MISFEES_SRCH
RE_OPTION_SRCH
RE_PTYTYPE_SRCH
RE_SECTYPE_SRCH
RE_SLSCAT_SRCH
RE_SUNDCHG_SRCH

Changes

 

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
Changes
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.