I recently did a 1-week consulting engagement in NYC (as a software product company, not something in our standard business model, but a blog reader wanted me to help them implement the techniques in the following entry, and besides, money is money 😉
As we followed the steps of creating a materialized view and registering it as a summary ledger, we discovered that not all the chartfields in the detail ledger were not showing up in the dialogs in GL for mapping to the summary ledger. I asked them to open up the record definition of the ledger table, and, sure enough, the field in question was not in the proper place in the record definition to be recognized as a chartfield (in fact, it was the last field in the record).
There’s a little known tip (that was much more important in the early days when every customer had to customize the chart of accounts by adding new fields). As long as you put the field in the proper location in the record definition of the journal line and ledger table, all processing in GL will automatically adjust.
For the both the LEDGER and JRNL_LN tables, the field needs to be between the LEDGER field and the CURRENCY_CD field.
The formal recommendation from PeopleSoft is that if you cannot relabel the expansion fields delivered with the product, to use the CF subrecords to add these fields. Because the CF subrecords are placed between these fields, the existing logic works. This customer preferred to add the fields directly to the ledger and jrnl_ln tables, so moving the field to the proper place solved the problem.