Please note that not all fields on a form directly reference columns in the database. Some fields are derived from database procedures, code in the forms or libraries, etc.
1. Use Help > Record History
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Record History
- The name of the base view used displays as Table Name:
- Go to the Sql*Plus
- Type desc , for example, desc mtl_system_items_fvl
- The columns of the table are displayed
- Review the columns and see if one matches the field that you are reviewing
(Note: if you see "Record history is not available here", it is normal per note 313678.1)
2. Use Help > Diagnostics > Examine
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Diagnostics > Examine (enter APPS password)
- The block and field name that contain the data in the form are displayed as well as the value in the field.
- Many times the block name is similar to the table name in the database
- Many times the field name is similar to the column name in the database
- Also you can go back to the view in #1 and see if the column can be found
Above are copied from Metalink Note 259722.1, but usually extra steps are needed to get what you want.
When I tried to trace a sales order by navigation:
a). log into application with Responsibility = Order Mgmt Super User
b). Navigate Orders,Returns > Order Organinzer
c). Enter sales order 1000xxx, press Find
d). See Order Organizer with one row returned.
e). Then with cursor on row for order, press Open. Get Sales Order form
f). Use Help > Record History, About This Record window shows "Table Name: OE_ORDER_HEADERS_V".
I ran "select * from apps.OE_ORDER_HEADERS_V" and got 0 row returned. By using Toad, I saw this view is created by joining 37 tables! My bet on getting information on the sales order was a table called oe_order_headers_all.
g). The Sales Order form shows Salesperson, But I need the SALESREP_ID. So I place the cursor on Salesperson field
h). Select Help >Diagnostics> Examine, enter apps password
i). The new window gives "Block: ORDER, Field: SALESREP_MIR, Value: a_name". Note that ORDER is not close to a table or view name at all.
j). Place cursor next to Field and click on LOV, then find SALEREP_ID and press OK.
k). The retured value is for SALESREP_ID. It tells me that ORDER is a block name on the Oracle Form and there are other data fields in the block without being displayed.
Below are other ways to do the trace by the Metalin Note 259722.1:
4. Research the view
- Sometimes the steps in #1 and #2 lead you to a view
- Get more details about the view as well as the underlying tables and columns
with the following SQL:
set long 100000
col text format a70
set pages 100
spool view.lst
SELECT VIEW_NAME, TEXT
FROM DBA_VIEWS WHERE VIEW_NAME = UPPER('&VIEW_NAME')
and owner = 'APPS';
spool off
5. Use SYSTEM.LAST_QUERY
- Query the records in a given form
- Choose Help > Diagnostics > Examine > Select LOV: Block = SYSTEM, Field= LAST_QUERY
- The query run to get the data shown is displayed.
- You can look at the various columns and tables used and research further
6. Run a trace
- Open the form that you are interested in but do not run a query
- Choose Help > Diagnostics > Trace > Trace with binds
- The trace file name and location are listed. Note them down
- Query the records
- Immediately shut trace off with Help > Diagnostics > Trace > No Trace
- The trace file name and location are listed
- Go to the database machine and get the trace file
- Create a tkprof of the trace file with the command, tkprof sys=no
- The various queries run are displayed.
- You can look at the various columns and tables used and research further
7. Open the form in Oracle Developer
- Open the form that you are interested in the Oracle Applications
- Choose Help > About
- Note the form name
- You have to first configure Oracle Developer to open forms with their associated libraries
- Once this is done Open Developer
- Open the form in question
- Review the block, pl/sql procedures and libraries associated to the form.
No comments:
Post a Comment