Designing Your Own Audit Report (Oracle HRMS Help)


	Previous	Next		Contents	Index	Navigation	Glossary	Library

Designing Your Own Audit Report

If you want to design your own audit report, you can copy the supplied report and use it as the basis of your own version. The supplied report uses a dynamic SQL structure to retrieve information for the report, and an Oracle Report to format it.

Dynamic SQL Structure

For a non-datetracked table, the dynamic SQL structure retrieves information and uses a private procedure to retrieve the new values for an update.

For a datetracked table, the dynamic SQL structure retrieves information from the function and procedure created for each datetracked table by the Audit Trail Update Datetracked Tables process:

The <tablesname>_TT function identifies the type of datetracked transaction represented by each row of the shadow table.

The <tablename>_VP procedure gets new values from the shadow table for update and insert transactions. It writes these to the HR_AUDITS and HR_AUDIT_COLUMNS tables.

The dynamic SQL structure comprises several character strings, which are concatenated into one long string, then parsed and executed using the dynamic SQL generator. If you change a string, you must ensure that you do not exceed the text space allocated to it, as listed in the package header.

The structure is built up by identifying the audited table column information, which is retrieved from the FND_AUDIT_COLUMNS table. The logic varies depending on the data type being audited.

The package for the dynamic SQL is called py_audit_report_pkg. It is found in the script pyadyn.pkh/pyadyn.pkb.

HR Audit Tables

The HR_AUDITS table holds audit information about the transaction. The HR_AUDIT_COLUMNS table holds the changed column information for that transaction. The structure of these tables is as follows:

HR_AUDITS Table

COLUMN                        TYPE          NULL
---------------------------   ------------  --------
audit_id                      number(15)    not null
commit_id                     number(9)     not null
current_session_id            number        not null
primary_key                   varchar2(30)  not null
primary_key_value             number(15)    mot null
sequence_id                   number(9)     not null
session_id                    number        not null
table_name                    varchar2(30)  not null
timestamp                     date          not null
transaction                   varchar2(30)  not null
transaction_type              varchar2(30)  not null
user_name                     varchar2(100) not null
effective_end_date            date          null
effective_start_date          date          null

HR_AUDIT_COLUMNS Table

COLUMN                        TYPE          NULL
---------------------------   ------------  --------
audit_id                      number(15)    not null
column_id                     number(9)     not null
column_name                   varchar2(30)  not null
new_value                     varchar2(240) null
old_value                     varchar2(240) null

Contents

Index

Navigation

Glossary

Library