I may have just re-invented the wheel but I don't think I've seen anything like this before so I thought I would share on a sunny Friday afternoon.
We have a few report parameters and list criteria that reference Section. Unfortunately using TR_SECTION as the table behind the dropdown is awkward because you don't know which facility things are in. We have a couple hundred facilities and a largish consortium with section names being re-used. Making things more complicated, most of our facilities are control grouped to the admin group and not the specific orgs that use them.
So I created LVS_FACILITY_SECTION (available on my profile page http://goo.gl/caqXPp). It ties together the facilities and the sections used in each and uses VS_PERF for control grouping.
Results are returned in less than a second so it works well for dropdowns. Hope this helps folks. And if I missed something standard to achieve the same thing, let me know! (Just don't tell the boss I spent an hour building this one :) )
- Levi