My Oracle Support Banner

Troubleshooting Chartfield Configuration Creating SQL Definitions With Incorrect Chartfield Order (Doc ID 791183.1)

Last updated on AUGUST 19, 2021

Applies to:

PeopleSoft Enterprise FIN Receivables - Version 8.8 to 9.2 [Release 8.8 to 9]
Information in this document applies to any platform.
*** Proactive Support Update 22-Jul-2011 ***
*** Checked for Relevance on Wed 14-Oct-2015 ***


Purpose

PeopleSoft Receivables Home <Document 1299436.1 > PeopleSoft Receivables Troubleshooting Primary Case Studies <Document 1272622.1>

Chartfields are fields that store chart of accounts information and provide the basic structure to segregate and categorize transactional and budget data used throughout Oracle Peoplesoft Enterprise applications.

The SQL definitions for chartfield objects are stored in PSSQLTEXTDEFN and varies for each installation depending on the chartfield configuration (active or inactive) and chartfield inheritance setup. The SQL definitions may be modified via online setups, such as when defining External Bank Accounts, or mass updated by batch process, through FS_CONFIG, or Standard Configuration for chartfields.

In Receivables, the ARUPDATE/AR_PGG_SERV process dynamically builds the SELECT and INSERT SQL statements from these SQL definitions. When delivered chartfields and inheritance setups are modified, and the batch process (FS_CONFIG) is used to propagate these changes, a defect may cause the order of chartfields to be stored incorrectly in these SQL definitions. This may result in chartfields mapping to incorrect fields, e.g. DEPT_ID value may map to FUND_CODE. Depending on which fields are involved, the problem may appear as data being incorrectly stored, or in the case of ARUPDATE processes, result in ABENDS when datatypes conflict.

Note that only the batch process FS_CONFIG creates these out of order SQL defintions. Online updates do not. This allowed a workaround to be used to correct the problem. For example, modifying and resaving External Bank Account setups will reorder the SQL definition for BK_CFI_FLST% objects, and modifying and resaving chartfield inheritance setups corrects the SQL definition for FS_CFI_FLST% objects.


This problem is addressed by GL development in <BUG 11545511> "CF Configuration is updating BK_CFI_* and FS_CFI_* SQL's with Chartfields in the wrong order." The issue is resolved in two parts:

1) Modify functions Bld_CF_Inh_SQL (in FUNCLIB_CF_FS.SQL_FUNCTION.FieldFormula) and Bld_Bank_CFI_SQL (in FUNCLIB_BANK.SQL_FUNCTION.FieldFormula) to order Chartfields correctly when re-creating the SQL's in batch mode.  This fix is delivered as <UPDATE 776001> FMS Financials 9.0 Bundle #22.  It is also available as a posted standalone fix <UPDATE 789592>. 

2) Created App Engine program FSCF_FIX_CFI for POC Resolution to mass-fix SQL's if customer does not want to run CF Configuration again.  Note this fix will be available as Update 789593 and is currently undergoing QA testing.  To see the current status, view  <BUG 11545511> and find update id 789593 in the list.

The fix in <BUG 11545511> is for v9.0 only.  However, the troubleshooting steps and workarounds described below, should apply to other versions.
For 9.2, a new approach is taken with <Bug 18287847> - INSERT AND SELECT SQL IN STEP (AR_PGG_SERV.SEQ_NUM.INSCFINH) HAVE INCORRECT CHAR

The new approach is to not have ARUPDATE code dependent on the order of chartfields in the SQL definitions, meaning when the dynamic SQLs build the field lists, it will use code where the chartfield order does not matter. This is available starting with 9.2.006 PUM image.

 

 

Troubleshooting Steps

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
Purpose
 PeopleSoft Receivables Home <Document 1299436.1 > PeopleSoft Receivables Troubleshooting Primary Case Studies <Document 1272622.1>
Troubleshooting Steps
References

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