Blog

Querying the OA Framework Personalization Repository

Have you ever struggled with the OA Framework Meta Data Store query utility package JDR_UTILS? In this blog article Barry Goodsell describes a PL/SQL package that build on JDR_UTILS in a way that makes the Personalization Repository simple to query and understand.

Nearly all E-Business Suite web-based screens are built using the Oracle Application Framework, normally referred to as OA Framework or OAF. This framework uses the Model-View-Controller pattern to separate the various layers of functionality. In the Model and View layers most of the functionality is built declaratively using the wizards in JDeveloper; it is only in the Controller that one has to resort to some Java code .

JDeveloper generates XML files for the Model and View that must be copied to the Application Server. The Model files are just stored in the file system, but the files defining the Views are uploaded into the Meta Data Store (MDS) – a set of tables with the prefix “JDR” stored within the APPLSYS schema of the EBS database. The XML from the View files is “shredded”, ie: records are created in the JDR tables for each and every XML element and attribute, linked together in a parent-child relationship.

One of the great things about the OA Framework is the ability to personalize the web user interface. This allows implementers and end-users to change the user interface at run time, layering personalizations on top of the base UI definition. The personalizations are also stored in the MDS which is queried at run time to build up the UI that is required for the current “context”, the combination of the current EBS Function, Industry, Location, Site, Organization, Responsibility and User.

Oracle provides a package called jdr_utils to help implementers retrieve information about MDS objects. There are many good blog articles already about using jdr_utils, so I am not going to go over the same ground. My problem with jdr_utils is that all the output is pushed to the DBMS output, which reduces it’s potential usefulness. I thought that it would be very handy to be able to query the MDS using regular SQL; effectively a version of the listDocuments procedure in jdr_utils that returns a set of records, rather than outputting to DBMS output.

The best way to achieve this is to use a Pipeline Table Function (PTF). To use a PTF we need two declarations: a PL/SQL Record Type and a PL/SQL Table of those Records:

TYPE component_rec_type IS RECORD 
  ( component        VARCHAR2(4000)
  , path_type        VARCHAR2(30)
  , is_personalized  VARCHAR2(1)
  , creation_date    DATE
  , last_update_date DATE );

TYPE component_tab_type IS TABLE OF component_rec_type;

The PTF can then be declared as:

FUNCTION list_contents
  ( p_path IN VARCHAR2 )
RETURN component_tab_type PIPELINED;

The body of this function has been lifted from the listContents procedure in jdr_utils, but has been altered to populate a component_rec_type record and pipe it back to the calling SQL:

FUNCTION list_contents
  ( p_path IN VARCHAR2 )
RETURN component_tab_type PIPELINED
AS
  r_comp component_rec_type;
  l_doc_id jdr_paths.path_docid%TYPE;

BEGIN
  l_doc_id := get_document_id(p_path, 'ANY');

  -- Nothing to do if the path does not exist
  IF (l_doc_id = -1) 
  THEN
    RETURN;
  END IF;

  FOR r_docs IN (
    SELECT jdr_mds_internal.getDocumentName(path_docid) as component
    ,      path_type
    ,      path_seq
    ,      creation_date
    ,      last_update_date
    FROM ( SELECT path_docid
          ,       path_type
          ,       path_seq
          ,       creation_date
          ,       last_update_date
          FROM    jdr_paths
          START WITH path_owner_docid = l_doc_id
          CONNECT BY PRIOR path_docid = path_owner_docid) paths
    WHERE ( path_type = 'DOCUMENT' AND path_seq = -1) 
    OR ( path_type = 'PACKAGE' AND path_seq = 0) 
    OR ( path_type = 'PACKAGE' AND path_seq = -1 
    AND NOT EXISTS (
      SELECT * FROM jdr_paths
      WHERE path_owner_docid = paths.path_docid)) )
  LOOP
    r_comp.component := r_docs.component;
    r_comp.path_type := r_docs.path_type;
    r_comp.creation_date := r_docs.creation_date;
    r_comp.last_update_date := r_docs.last_update_date;

    -- Make package directories distinct from files. Note that when
    -- listing the document recursively, the only packages that are
    -- listed are the ones which contain no child documents or packages
    IF ((r_comp.path_type = 'PACKAGE') AND (r_docs.path_seq = -1)) THEN
       r_comp.component := r_comp.component || '/';
    END IF;

    IF (has_personalizations(r_comp.component) 
    OR r_comp.component LIKE '%/customizations/%')
    THEN
      r_comp.is_personalized := 'Y';
    ELSE 
      r_comp.is_personalized := 'N';
    END IF; 
    PIPE ROW (r_comp);

  END LOOP;

  RETURN;
 
END list_contents;

As you can see the above code makes calls to two other locally defined private functions: get_document_id and has_personalizations. The first function is a direct copy of the private function jdr_utils.getDocumentId. The second function borrows from jdr_utils.listCustomizations to return a Boolean that indicates whether the current object has personalizations.

A word about the terminology being used here: what JDR refers to as a customization is in fact a personalization. Customization often has negative connotations for customers, which is why I thought it important to use the more friendly “personalization” term.

So, how do we use the Pipeline Table Function? The TABLE(…) function is used in the FROM part of the SQL; the following SQL will list all OAF pages and regions that are used in Oracle Project Management’s Control Items screens:

SELECT mds.component
,      mds.is_personalized
,      mds.last_update_date
FROM   TABLE(xxprj_fnd_mds.list_contents('/oracle/apps/pa/ci/webui')) mds;

You can see in the screenshot that the Page CiCiCreatePG has a Personalization. It would be nice to find out a bit more about it and this is where the Pipeline Table Function implementation comes into it’s own, as we can apply any normal WHERE clause criterion – something that is impossible to do with the out-of-the-box JDR utility package:

SELECT mds.component
,      mds.is_personalized
,      mds.last_update_date
FROM   TABLE(xxprj_fnd_mds.list_contents('/oracle/apps/pa/ci/webui')) mds
WHERE  mds.component LIKE '%CiCiCreatePG';

What if we want to see the XML that is behind these two MDS objects? You can use the jdr_utils.printDocument procedure to output the XML to DBMS output, but this is of limited use if you wanted to query the XML further. Another way to obtain the XML is to use the exportDocument function, but this returns the data in 32K chunks and can only be called in PL/SQL.

The Oracle Database has very powerful native XML support in the XMLType data type so I thought it would be worth adding a function to return a MDS document in this format which could then be used in SQL. The XMLType data type has a variety of constructor functions, one of which accepts the XML document as a CLOB parameter. Using all this we can create two functions, the first to retrieve the XML as a CLOB:

FUNCTION get_doc_clob
  ( p_path IN VARCHAR2 )
RETURN CLOB
IS
  l_chunk VARCHAR2(32000);
  l_clob  CLOB;
  l_done  BOOLEAN;
 
BEGIN
  dbms_lob.createtemporary(l_clob, TRUE);
 
  l_chunk := jdr_utils.exportDocument(p_path, l_done);
  dbms_lob.append(l_clob, l_chunk);
 
  WHILE (NOT l_done) 
  LOOP
    l_chunk := jdr_utils.exportDocument(NULL, l_done);
    dbms_lob.append(l_clob, l_chunk);
  END LOOP;
  RETURN l_clob;
 
END get_doc_clob;

…and a second to use the first to create an XMLType:

FUNCTION get_doc_xml
  ( p_path VARCHAR2 )
RETURN XMLTYPE IS
BEGIN
  RETURN XMLType.createXML(get_doc_clob(p_path));
END get_doc_xml;

We can now change our query to also pull back the MDS document for the CiCiCreatePG:

SELECT mds.component
,      mds.is_personalized
,      mds.last_update_date
,      xxprj_fnd_mds.get_doc_xml(mds.component) as xml_file
FROM   TABLE(xxprj_fnd_mds.list_contents('/oracle/apps/pa/ci/webui')) mds
WHERE  mds.component LIKE '%CiCiCreatePG';

To properly see the XML, SQL Developer now has a handy XML Viewer built-in:

Now we can use SQL to access the MDS structure and the MDS XML contents, we can start getting clever and do some very useful things. With the following query we retrieve the names of all Controllers that are referenced anywhere in the Project Management Control Items screens: a very useful query for impact analysis where you might be thinking of extending a Controller:

SELECT mds.component
,      extractValue(value(ctl)
         , '/*[1]/@id'
         , 'xmlns="http://xmlns.oracle.com/jrad"') AS element_id
,      extractValue(value(ctl)
         , '/*[1]/@controllerClass'
         , 'xmlns="http://xmlns.oracle.com/jrad"') AS controller
FROM   TABLE(xxprj_fnd_mds.list_contents('/oracle/apps/pa/ci/webui')) mds
,      TABLE(XMLSequence(
         EXTRACT(xxprj_fnd_mds.get_doc_xml(mds.component)
           , '//*[@controllerClass]'
           , 'xmlns="http://xmlns.oracle.com/jrad" ' ||
             'xmlns:ui="http://xmlns.oracle.com/uix/ui" ' ||
             'xmlns:oa="http://xmlns.oracle.com/oa" ' ||
             'xmlns:user="http://xmlns.oracle.com/jrad/user"' ) )) ctl;

You can download the complete code for the xxprj_fnd_mds package using the following links:

• xxprj_fnd_mds Package Header
• xxprj_fnd_mds Package Body

8 Aug, 2012 by Barry Goodsell

E-Business Suite | Technical discussion