Tuesday, January 24, 2012

SQL Behind R12 Self Service Application Web (OA Framework) Page

In R12 with the Oracle’s new OA framework one can find the sql behind the html or web page. Oracle has given almost the same functionality in the “About this Page” link. If you want to find the SQL query behind any web page, you need to enable personalization and then use “About this Page” to find the view object. Below is an example for daily rates page from the GL module:

Step 1: Enable the personalization profile
   a) Under the system admin responsibility, open the system profiles form
   b) Change the “Personalize Self-Service Defn” profile to yes for the responsibility

Step 2: Perform the business process as you get into GL responsibility as below:
   a) Using the GL Super User responsibility, open the Daily Rates web page:
           >> setup >> Currencies >> Currency Rate Manager >> Daily Rates
   b) Find your daily rates for a combination of given options

Step 3: Click the About the Page link

Step 4: Click the Page tab and then click the Expand button on the business components section

Step 5: Find you view object and click the link

Step 6: Select the SQL query and copy it to your notepad (TOAD, SQL Developer,..)

Step 7: Replace any variables (anything that has a colon in front of it) with your data and test

Step 8: Modify the query and use it.

No comments: