Sub Menu

Archives

Recent Comments

Hidden Flexfield Values (Table Validation)

Written by Peter vollebregt on 13/07/2016. Posted in Blog

Although I have been teaching Flexfields for Oracle University sometimes you learn new things only when you practice them.

While working in Oracle Enterprise Asset Management on a Table Validated Segment I discovered I could not see any of the disabled values of the Underlying Suppliers Table. After a long search I discovered a well hidden fact: Oracle actually can and will change the where clause of your implicit SQL statement without showing it IF the developers found this meaningful AND without telling you so. This can happen to you in any table-validated flexfield and you will never know untill you test.

There is however a solution for this: If you add a table alias to the table name the implicit where clause is disabled!

The nasty details in my case

This was during a R12 upgrade where I had to change the underlying suppplier table from PO_VENDORS to AP_SUPPLIERS due to the transfer to TCA. But the unwelcome side-effect was that it was no longer possible to link a disabled supplier to an asset. Leading to data-corruption and errors when trying to update an existing Asset.

The solution was simple to add the Table Alias “APS” to the table, and suddenly the disabled values where available again ………..