Wednesday, January 8, 2014

R12: Menus and You (or how they got changed and you can change them back)

The other day I had to investigate an issue with an external application, which has an authentication against our EBS system, but when I tried to look at the Form we used to control the access hierarchy it wasn't there any more.  I wasn't really surprised, but what did surprise me was that the Menu we used to attach the Form had been recreated by the FND_USER.USER_ID 121 which I posted about a few days ago shows up as the FND_USER.USER_NAME of ORACLE12.1.0.  I wasn't seeing things, in our archive 11i EBS instance the menu existed many years ago created by AUTOINSTALL but after R12.1.3 it says it was created only a few years ago by ORACLE12.1.0.  I would share the menu in question, but when I did a search to see how many menus were either CREATED_BY or LAST_MODIFIED_BY 121 I received a LOT of hits.  Thousands.  So, add the Form back to the Menu and we're done right?  Not exactly.

The View FND_MENU_ENTRIES_VL ties together FND_MENU_ENTRIES and FND_MENU_ENTRIES_TL but much to my surprise, it ties these tables together based on the ENTRY_SEQUENCE field.  So what you're wondering right?  Well our old Menu was ordered 1, 2, 3 but the R12.1.3 install made the same items 5, 10, 15 and I wanted it changed back before I added my Form back on there.  I had not looked closely at the View, so when I ran this script:

UPDATE FND_MENU_ENTRIES
SET ENTRY_SEQUENCE = 1
WHERE MENU_ID = 67867
AND ENTRY_SEQUENCE = 5

what do you think happened when I requeried the Menu?  The first entry was GONE.  Just plain gone!  I didn't freak out, but this is when I looked at the View and found out how the tables were linked so I had to run the script:

UPDATE FND_MENU_ENTRIES_TL
SET ENTRY_SEQUENCE = 1
WHERE MENU_ID = 67867
AND ENTRY_SEQUENCE = 5

before requerying the Menu and the entries that were 5, 10, 15 were now 1, 10, 15 and ready for me to change the 10 and 15 entries with the same set of scripts above for each entry.  This was my first attempt to undo a Menu change such as this, and it hurts my head to think of how many other Menus have been changed which we haven't stumbled upon yet, but knowing the tables involved it sure seems like we could start building some alerts to let us know the next time a Menu might change right?

Also, iff you still need to upgrade, this should be on your list of "gotchas" that we've been slowly building over the last few months for how R12 is different or changes your system!

No comments:

Post a Comment