A good friend of mine is an excellent Oracle Trainer / Consultant. I noticed an article on his blog about rewriting queries on the fly. Quite interesting stuff. Similar to method interception in Aspect Oriented Programming, a query can be substituted over another in real time.
Like it or not, there are millions upon millions of lines of software code where values and rules are hardcoded. Using DBMS_ADVANCED_REWRITE isn't for the feint of heart. If a system had embedded or hard coded rules and needed to change over to a new set of rules, this technique could help ensure compliance and speed up the development time.
Should a system be built around this method? Certainly not, but if a legacy system needs to be updated quickly and accurately, this technique is gold in the right hands.