Hi Shaun,

I would try loading the screen with the debug console open in the first instance - this works for most screens.

Otherwise use an Oracle IDE such as Oracle SQL Developer, login as IFSAPP and search for views beginning with FND. You should be able to find the ones you are looking for based on the name and a quick SELECT * FROM…

Cheers,

Pete

Log in to the database as the application owner.

All views:

SELECT * FROM user_views;

All tables:

SELECT * FROM user_tables;

Views depending on tables:

SELECT * FROM user_dependencies WHERE type = 'VIEW' AND referenced_type = 'TABLE';

How did I find these? Oracle has a list of dictionary views for everything that’s possible to see in the system metadata. It will at least give you terms you can Google.

SELECT * FROM dictionary;

The views that start with USER_ are for the current user, which is what you’ll likely want to use when logged in as the application owner. ALL_ views show everything that is visible to the current user. DBA_ views show everything that is available in the entire system. (This can be confusing. If you really want everything, you want DBA_, not ALL_, but on a properly configured system, the application owner can’t see everything. Short of logging in as SYS all the time, which is bad practice, it’s helpful to create an IT permission set in IFS, then on the underlying Oracle role, GRANT SELECT ANY DICTIONARY TO it_team; )

Answering the specific questions:

SELECT * FROM batch_schedule_method_tab; -- Database Tasks

SELECT * FROM batch_schedule_method_par_tab; -- Database Task parameters

SELECT * FROM batch_schedule_tab; -- Database Task Schedules

SELECT * FROM batch_schedule_par_tab; -- Database Task Schedule parameters

SELECT * FROM transaction_sys_local_tab; -- Background Jobs

SELECT * FROM transaction_sys_status_tab; -- Background Job status lines

I agree with Pete that the Debug console is the best starting point, so something like Background Jobs will show as coming from the appowner DEFERRED_JOBS view, but I think the difficulty with some of the Solution Manager area screens is that they may be using SYS owned objects, e.g. SYS.DBA_SCHEDULER_JOBS.  I suspect these are the ones that don’t tend to show even in the Debug console.

The debug console will show you the view, but often the framework isn’t configured to display it. For example, that method won’t work for Batch Queue Configuration in Apps 9.

Sometimes, there are flaws in the views that justify viewing the tables directly. In your example, DEFERRED_JOB has two WHERE predicates that slow down the view so much it’s almost unusable for daily use. First, it does a check against SECURITY_SYS.HAS_SYSTEM_PRIVILEGE, which isn’t marked DETERMINISTIC in the code, relying on the spotty intelligence of the query optimizer to cache it. Second, it compares your session username against the table USERNAME column, but TRANSACTION_SYS_LOCAL_TAB.USERNAME isn’t indexed by default. A simple SELECT * query shouldn’t take that long.

I don’t recommend everyday end users querying the raw tables, since this works around all row-level security and exposes the possibility of manipulating the data outside of the business logic, but this method does have its place for an IT user.

> SELECT * FROM transaction_sys_local_tab; -- Background Jobs

> SELECT * FROM transaction_sys_status_tab; -- Background Job status lines

I would suggest not using the above 2 Tables as a point of reference. 

IFS screens (almost?) always pull from Views rather than Tables, and using the Debug console I can see that Background Jobs are pulled from the DEFERRED_JOB view in Apps9.  There is a matching DEFERRED_JOB_STATUS view too.

Totally agree. 

After an upgrade, a view might reference a new table, or a column may get remapped. This can be confirmed by reviewing the view definition.

SELECT dbms_metadata.get_ddl('VIEW', 'DEFERRED_JOB') FROM DUAL;