Create an Audit Region based on a PL/SQL Region

In my APEX application I wanted to have a small region in all screens which would enable the user to see the audit columns CREATED, CREATEDBY, UPDATED and UPDATEDBY which are present in all tables. Like the screenshot below shows, in the report I added a dummy column “Audit” with a link. When the user clicks on the audit link in a record, the Audit window in the upper right hand corner appears with the audit columns of the record the user selected.

This region can be created manually with items, but instead of manually creating this region including the items on each page, I developed a small procedure which shows the audit data automatically, based on a simple configuration.

The only condition is that each table contains a one-column primary key which also has to be present in the reports in the screens.

Once you have created the PL/SQL region in one screen in your application, you can copy the region to all the other screens, and it requires minimum configuration to make it work in all the other screens.

See below the steps to create the Audit region and make it work. First you need to create a PL/SQL region that contains a hidden item “P??_AUDIT_ID”.

1 Create a Region.
1.1 Type: PL/SQL Dynamic Content.
1.2 Region Details :








1.3 PL/SQL Source:
apex_custom_utils.show_audit(:APP_ID,:APP_PAGE_ID, ‘TABLENAME’, :P45_AUDIT_ID );


Replace TABLENAME with the tablename of your report, and change P45 to your page number.

The code of the procedure show_audit will follow further below.






1.4 Set the condition of the region (Only show the region is P??_AUDIT_ID is not null)















2 Create an item P??_AUDIT_ID in the newly created region:
2.1 Item Type:





2.2 Item Display Position and Name:








2.3 Item Attributes:








2.4 Source:












2.5 Press button “Create Item”

3 Add dummy column “Audit” to Report Region:
3.1 Add the following column to the query: ‘Audit’ as AUDIT_BUTTON









4 Edit the dummy column:
4.1 Set the heading blank (& nbsp;)





4.2 Set the column link:









The target page is the page itself.
The item P??_AUDIT_ID will receive the value of the primary key column of the report, in this case ALT_ID.

5 Create a procedure in the database. In this case I created the following procedure in a package:
--------------------------------------------------------------------------------------------
PROCEDURE show_audit( p_app_id IN NUMBER
, p_app_page_id IN NUMBER
, p_tablename IN VARCHAR2
, p_id IN NUMBER ) IS
--
CURSOR c_get_pkcol( c_tablename VARCHAR2 ) IS
SELECT col.column_name
FROM user_cons_columns col, user_constraints cons
WHERE cons.constraint_type = 'P'
AND cons.table_name = c_tablename
AND col.constraint_name = cons.constraint_name;
--
cpagecomment VARCHAR2(4000);
ctablename VARCHAR2(100);
cpkcolumn VARCHAR2(100);
cstatement VARCHAR2(2000);
cursor1 INTEGER;
rows_processed INTEGER;
dcreated DATE;
ccreatedby VARCHAR2(50);
dupdated DATE;
cupdatedby VARCHAR2(50);
BEGIN
IF p_app_id IS NOT NULL AND p_app_page_id IS NOT NULL
AND p_tablename IS NOT NULL AND p_id IS NOT NULL THEN
open c_get_pkcol( p_tablename );
fetch c_get_pkcol into cpkcolumn;
close c_get_pkcol;
--
cstatement := 'SELECT created, createdby, updated, updatedby'||
'FROM '||p_tablename||
' WHERE '||cpkcolumn||' = '||TO_CHAR(p_id);
--
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse( cursor1, cstatement, 2 );
dbms_sql.define_column( cursor1, 1, dcreated );
dbms_sql.define_column( cursor1, 2, ccreatedby, 30 );
dbms_sql.define_column( cursor1, 3, dupdated );
dbms_sql.define_column( cursor1, 4, cupdatedby, 30 );
rows_processed := dbms_sql.execute( cursor1 );
IF dbms_sql.fetch_rows( cursor1 )>0 THEN
dbms_sql.column_value( cursor1, 1, dcreated );
dbms_sql.column_value( cursor1, 2, ccreatedby );
dbms_sql.column_value( cursor1, 3, dupdated );
dbms_sql.column_value( cursor1, 4, cupdatedby );
END IF;
dbms_sql.close_cursor( cursor1 );
--
htp.prn('<table class="formlayout">');
htp.prn('<tr><td nowrap align="right">Created :'||
'</td><td nowrap align="left">'||
TO_CHAR(dcreated,'DD/MON/YYYY HH24:MI:SS')||'</td>< /tr>');
htp.prn('<tr><td nowrap align="right">Created by :'||
'</td><td nowrap align="left">'||
ccreatedby||'</td></tr>');
htp.prn('<tr><td nowrap align="right">Updated :'||
'</td><td nowrap align="left">'||
TO_CHAR(dupdated,'DD/MON/YYYY HH24:MI:SS')||'</td>< /tr>');
htp.prn('<tr><td nowrap align="right">Updated by :'||
'</td><td nowrap align="left">'
||cupdatedby||'</td></tr>');
END IF;
EXCEPTION WHEN others THEN
htp.prn('Error trying to display audit values : '||SQLERRM||'<br>');
END show_audit;
--------------------------------------------------------------------------------------------

6 - Done ! When you click on the Audit link in the report, the Audit region should appear in the upper right hand corner with the audit data.

7 To make it a bit more “neat”, make sure to clear the cache of this page when you navigate to another page. So that when you navigate back to the page, the audit region doesn’t already appear without actually haven clicked on any records.

8 To deploy the Audit Region to other pages in your application, simply copy the Region including items to other pages. The only things you have to configure after copying the region are:
- The tablename in the PL/SQL source procedure call
- Make sure there is a dummy column “Audit” with a link in the report region which fills the P??_AUDIT_ID item.