Find data sources in Oracle EBS Environment (OAF article)

http://oracleseeker.com/2009/08/12/how_to_find_the_table_view_of_oaf_application_in_oracle_ebs/

The data source behind the Oaf page is more cumbersome than it is in the form, and cannot be positioned as accurately as it does, but it can also find most of the content.

This article describes how to use the diagnostic features provided by OAF to quickly find the corresponding database objects for a oaf page:

1. About the features on this page
2. SQL Trace Feature

The use of this page function (about this page)

Prerequisite: Set the prefab file at the user level:Personalize self-service Defn The value of Yes to start personalization mode

Steps:

1. Click on the "about this page" link in the lower left corner of the Oaf page
2. Go to the page structure on the page Definition section, and the structure of the page will be different with the page definition
3. Or select the View object definition in the business Component References details section
4. View the information in the View Object column, which contains links to the view objects referenced by the component, and clicks directly into the View object page
5. The SQL in the View object details is the statement that creates the View object, and you can see the background data source that corresponds to the page component.
6. View the corresponding definition by selecting a different View object

Instance (find the corresponding background database object in the PO Document types page):

1. Click the About this page link in the bottom left corner of the document types
   
2. After entering the About page, expand the page Definition section to the following structure
   
3. Click the Poalldocumenttypesvo View Object link to enter the View Object Definition Detail Page
   
4. Alternatively, you can expand business Component References Details on the About page pages Then select the following view object: Oracle.apps.po.setup.server.PoAllDocumentTypesVO
   
5. SQL can be obtained from the View Object Detail page

? View Code SQL

Second, using SQL Trace function

Whenever a database operation occurs, you can use the SQL Trace feature to track database operation statistics, and the Oaf page also needs to perform additions and deletions to the database, so you can use it to track.

Prerequisite : Set the pre-fabricated file at the user level:fnd:diagnostics Value Yes, enter diagnostic mode

Steps :

1. Select Diagnostic as Set Trace level and click the Go button
2. Set the level of SQL Trace
3. Proceed to the Oaf page and ensure that the action is queried, inserted, updated, or deleted
4. Once the operation is complete, go to close trace mode again and record the generated trace ID number
5. Log on to the database server as a database operating system user, such as ORA02
6. Format trace results file output using the Tkprof tool
7. Viewing a trace file that has been formatted
8. Draw Data Source

Instance:

2. Set up the pre-fabricated files at the user level as a system administrator: Fnd:diagnostics value Yes, enter diagnostic mode
   
4. Click the Global button at the top of the page to log in to the same user as the preset file settings diagnostics
   
6. Optionally, select a few options for trace to enter SQL Trace mode and click the Save button
   
8. Go to PO Document Types page Update forward method after saving
   
10. Enter diagnostics again to close trace, record the last trace ID number generated under 16456
    
12. Use the following SQL to query the path of the trace file
    SELECT value
    From V$parameter
    WHERE name = ' User_dump_dest ';
    VALUE
    ———————————————————————–
    /d02/db/tech_st/11.1.0/admin/vis02_ebs006/diag/rdbms/vis02/vis02/trace
14. Telnet to the database server with the database operating system user (ORA02) and run the following command
    [Email protected] ~]$ Cd/d02/db/tech_st/11.1.0/admin/vis02_ebs006/diag/rdbms/vis02/vis02/trace
    [Email protected] trace]$ Ls-al *16456*
    -rw-r–r–1 ora02 dba 116421 17:48 16456_234_1.output
    -rw-r–r–1 ora02 dba 502101 17:35 VIS02_ORA_16456_234.TRC
    -rw-r–r–1 ora02 dba 17288 17:35 VIS02_ORA_16456_234.TRM
    [Email protected] trace]$ tkprof vis02_ora_16456_234.trc 16456_234_1.output
16. The above command generates the 16456_234_1.output file, and after downloading the file from the server, find the UPDATE statement in the file, where the update action for the ICX type does not concern, the 16456_234_1.output file sample
18. Find the following UPDATE statement

? View Code SQL

12345678

UPDATE po_document_types_all_b podocumenttypeeo SET last_update_date =: 1, last_updated_by =: 2, Forwarding_mode_code =: 3, last_update_login =: 4 WHERE org_id =: 5 and Document_type_code =: 6 and Document_subtype =: 7

–

Find data sources in Oracle EBS Environment (OAF article)