Almost every big client runs out of flexfields in a few key tables. The flexfields in these tables are often considered to be prime real-estate. Most clients either settle with sub-optimal solutions or create a custom table for flexfields and create a form to maintain the custom table, which is a fairly expensive customization.
We came with this idea where we could create a fairly simple solution, which would use all the standard flexfield features that Oracle provides and yet make it very simple to extend flexfields on, pretty much any table. The cool thing was that the idea was implemented within 3 days.
There were some concerns about the performance intially, but it worked out pretty well at the end.
The title “Extending Flexfields” is a link to the white paper on the solution.
We added some more cool features to this solution after this white paper was written. (The solutions should be pretty obvious once you have read the White Paper).
- We added an action field to the extended flexfield form, which could be used for any user defined action. Basic concept was the same. We had a table that had all the actions and associated PL/SQL procedures/functions. The forms were linked to actions (so that the actions were available only on appropriate forms). The Primary Keys were used as input to the PL/SQL routine associated to actions. (eg. you want to run certain custom validations on user requests for an order – this would let you do it, if you wanted user to be able to send certain pre-formatted notification – this would let you).
- We added multiple rows to the extended flexfield form. Basically it would allow you to have multiple (set of) values of the same flexfield for one parent record.
I have the entire code for this, if any one is interested do let me know.