How to find Personalizations

You have lots of personalizations in your system done by lots of nerds and there is no documentation. How do you figure out whats in without much efforts? Of course you could use export tool from the Functional Administrator and you would get everything under the sun. But what if you just want to know all the personalizations for ORG 1234? So here you go, here is how you do it.

There are only a handful tables where the XML MDS information is stored, and these are the tables

JDR_PATHS
  JDR_COMPONENTS
  JDR_ATTRIBUTES
  JDR_ATTRIBUTES_TRANS

And there is one absolutely useful package JDR_UTILS.

First step is we got to figure out some path names for this ORG 1234.

1. Execute this one first, we get a path id from this to get a qualified name later on

SQL> SELECT path_docid FROM JDR_PATHS WHERE PATH_NAME = '1234';
===> 12345

2. Get the full qualified path name

SQL> SELECT JDR_MDS_INTERNAL.GETDOCUMENTNAME(12345) P FROM DUAL;
===> /oracle/apps/ap/oie/entry/lines/webui/customizations/org/1234

3. Now that we got the path, we can get individual documents

SQL> SET SERVEROUT ON
SQL> EXEC jdr_utils.listDocuments('/oracle/apps/ap/oie/entry/lines/webui/customizations/org/1234');

Printing contents of /oracle/apps/ap/oie/entry/lines/webui/customizations/org/1234
/oracle/apps/ap/oie/entry/lines/webui/customizations/org/1234/CashAndOtherLinesPG
/oracle/apps/ap/oie/entry/lines/webui/customizations/org/1234/DetailsPG


PL/SQL procedure successfully completed.

SQL>

Bingo! and done.
You can do same stuff for responsibilities, functions etc. Just replace the ORG_ID with the appropriate IDs.

Cheers