We are starting a new initiative to talk about some of the little known but practically useful features that have been delivered by Oracle | PeopleSoft . We plan to post one new discussion every week on Friday. We are starting this series with a post on the Table usage SQR process. Please read through the post below for more information.

 If you would like us to talk about specific delivered features , please add a comment on this post and we will endeavor to meet your expectations! 

-  Expenses & Resource Management Communities Moderators.

Troubleshooting processes and application has always been a challenging job which turns more complex when it comes to applications with multiple procedural calls and nested looping. Engineers always look for the actions performed such as insert/update/delete on the various tables involved in the processing. Keeping a note and track of all this becomes tedious.

PeopleSoft has delivered a utility “Tables Accessed and Updated Report” which gives you a list of all the records and actions performed on them during a program execution. It is a SQR report designed to understand the behavior of PeopleSoft applications such as App Engines, SQRs, COBOLs and Queries with the underlying transaction records.

It basically performs two actions:

• Reports the record names referenced by the SQL statements within the applications.
• Reports the way in which those records were accessed (for example, Select, Update, or Delete.)

The output information can be further used to:

• Understand a program's functionality and how it manages your data.
• Assess the impact of proposed customizations.
• Determine the underlying database privileges require to execute a program.
• Identify every batch process that uses or alters a specific record.

This report can be found on the following navigation:

• Set Up Financials/Supply Chain >> Common Definitions >> Tables Accessed and Updated
• The report page is nothing but a Run Control page where a user can provide inputs for analysis. One can select the application type and search for the available programs. Once selected, all these are added to selected list which goes as an input for analysis to the report.
• A scenario where all the App Engines for a particular product, let’s say AP, are to be analyzed, then instead of selecting all the AP processes, user can just enter AP in application name field and run the report. It will give the output for all AE starting with AP. That is just another way of using this tool.

Benefits:

• A big time saver
• No manual track and note
• Handy PDF outputs

Restrictions and limitations:

• The SQR allows the use of dynamic substitution tables.

Since the Tables Accessed and Updated report can't know the full set of tables that might be substituted, it reports the condition when it encounters this kind of SQR.

• The SQR incorporates the construction of SQL clauses within string variables that can then be used in SQL statements and resolved by the SQR at runtime.

The Tables Accessed and Updated report can't detect this style of coding, however there aren't many table references found within these kinds of clauses.

• The SQR references libraries through syntax that includes (#INCLUDE) SQC files.

If an included module is a very generic library with a large number of general-purpose routines in it, the analysis may overstate the number of tables referenced.

• The COBOL programs reference libraries through syntax that calls (CALL ... USING) routines in other modules.

If the module called is a very generic library with a large number of general-purpose routines in it, the analysis may overstate the number of tables referenced.

• The Application Engine program contains the meta-SQL phrase %Execute() with platform-specific procedural logic (such as Microsoft SQL Server "Transact-SQL" or Oracle "SQL-Plus").

The Tables Accessed and Updated report reports such usage as unanalyzed.

• The Application Engine program uses a PeopleCode step.

The Tables Accessed and Updated reports any use of PeopleCode as unanalyzed, whether or not it contains SQL statements.

• The Application Engine program contains forms of meta-SQL that contains dynamic object references.

Since the Tables Accessed and Updated report can't know the full set of tables that might be substituted, it reports the condition when it encounters this kind of meta-SQL.

Additional information on how to use it can be found in the screenshot document attached.