Auditing Downloads from Interactive Reports

Since the Data Protection Act (DPA) came into force auditing of the viewing and downloading of personnal information has become an issue.  The DPA allows for the use of personal information so long as there is a valid buisiness reason and that appropriate measures are taken to protect that information.  Auditing downloads is one way for an organisation to monitor who has taken information off-line and therefore out of their control.  These are the basic steps:

1.  Create an audit log table:

 ID (NUMBER)
APP_USER (VARCHAR2(60))
DOWNLOAD_DATE (DATE)
QUERY (VARCHAR2(4000))

2.  Create an Hidden and Protected item :P1_IR_REPORT_ID

3.  Install the apex_ir_query package by Stew Styker at

       http://stewstools.wordpress.com/apex_ir_query-package/

4.  Create a Page Precess

  Process point - Onload Before Header
  Condition Type - Request is Contained within Expression 1
  Expression 1 - CSV,RTF,XLS,PDF
  Source:

INSERT INTO DOANLOAD_AUDIT
   (APP_USER
    ,DOWNLOAD_DATE
    ,QUERY)
  VALUES
    (:APP_USER
    ,SYSDATE
    ,apex_ir_query.ir_query_where(:APP_ID
    ,<IR report page>
    ,:SESSION
    ,:P1_IR_REPORT_ID)

How to cope with Lists Of Values (LOVs)

Another problem that has been perplexing me is what is the best way to manage LOVs. Apex provides a variety of mechanisms for managing LOVs including static or dynamic LOV which can be specified centrally or locally to an item. Best Practice dictates that centrally managing LOVs is preferred where possible as it provides a single point of update.

I have found a few issues with the Static LOVs as follows:
• Discoverer Reporting. Viewing LOV values in Discoverer is tricky as you have to query the flows table. This can be done with the following view but you have to remember to change the names of the flows tables after an upgrade of Apex.

SELECT
DISTINCT lov.lov_name
,lovd.lov_disp_value
,lovd.lov_return_value
,lovd.lov_disp_sequence disp_sequence
FROM flows_030100.WWV_FLOW_LISTS_OF_VALUES$ lov
,flows_030100.WWV_FLOW_LIST_OF_VALUES_DATA lovd
WHERE lov.id = lovd.lov_id

• Conditions. It is possible to add conditions to each static LOV item, which in effect writes nested sub-queries that are difficult to manage and probably not very performant.
• Maintenance. Changing the values of the LOVs can only be done from the workspace, so a minor change to an LOV values can only be done as part of a release.
• Deleting an LOV item causes the ID from the Flows table to be returned (if display additional items is checked) or a null value is returned (If display nulls is checked) or return the first value in the select list. None of these options are ideal.

My preferred method of managing LOVs is to create my own custom table along the following lines:

LOV_ID (NUMBER)
DISPLAY_VALUE (VARCHAR2)
ELEMENT_NAME(VARCHAR2)
LOV_NAME (VARCHAR2)
SORT_ORDER (NUMBER)
VALID_FROM (DATE)
VALID_TO (DATE)
NOTES (VARCHAR)
EDITABLE (VARCHAR2)
ATTRIBUTE1 (VARCHAR2)
ATTRIBUTE2 (VARCHAR2)
ATTRIBUTE3 (VARCHAR2)
ATTRIBUTE4 (VARCHAR2)

I have added a few additional attributes columns, which are useful for storing values for reporting or conditional displays. Each LOV item has a validity period, which can be used to expire values so they do not appear as a selectable items in an LOV. However, as they still exist in the table they can be displayed in reports and as additional items. The element name is useful for writing scripts to update attribute values, as the ID could differ from your development environment and the display value may change. The notes field is used for storing information about the values set in the additional attributes.

To get invalid items to display as additional item in a Select List or popup lov use the following for your LOV query.

SELECT display_value
,lov_id
FROM lov
WHERE SYSDATE BETWEEN valid_from AND
nvl(valid_to ,SYSDATE)
UNION
SELECT display_value
,lov_id
FROM lov
WHERE lov_id = :p1_colour_id
AND lov_name = 'COLOUR'


Its quite nice to let the user know that the value has expired and needs changing. I like to do this by appending an asterisk to the display value with the following lov query.

SELECT nvl2(lov2.lov_id
,lov1.display_value
,'(' lov1.display_value ')') display_value
,lov1.lov_id
FROM (SELECT display_value
,lov_id
FROM lov
WHERE SYSDATE BETWEEN valid_from AND
nvl(valid_to,SYSDATE)
UNION
SELECT display_value
,lov_id
FROM lov
WHERE lov_id = :p1_colour_id
AND lov_name = 'COLOUR') lov1
FULL OUTER JOIN (SELECT display_value
,lov_id
FROM lov
WHERE SYSDATE BETWEEN valid_from AND
nvl(valid_to,SYSDATE))lov2
ON lov1.lov_id = lov2.lov_id

Example.

Next and Previous from an Interactive Report

The new feature of Interactive Reports (IR) in Apex provides the user with the ability to search, sort and filter reports easily, create their own versions of reports and save them for future use. One limitation with IRs is that I haven’t been able to find a satisfactory way of producing Next and Previous buttons on a custom details screen. The issue being how to best capture the Filters and Sorts that have been set in the IR. I have seen some very slick solutions using JS and AJAX techniques whereby the IDs of the records displayed in the IR are saved to an array and then referenced from the detail screen. Ajax is then used to re-query the main IR to get the next pagination set if required. The only issue I have with these techniques is that they are a bit involved to incorporate and require hidden items to be concatenated to existing fields. If those fields are then removed from the report by the user then that breaks Next Previous function.

I have written a procedure that reconstructs the query being run in the IR from the APEX views and then uses Analytics to return the Next, Previous, First, Last and Count values. It works pretty well and should be quick and easy to implement. The only failing I have found is when no sort is applied to the IR and the base query has no order by; in this case the order of the next and previous can be different to the order on the IR.

The complete solution can be viewed from the link below, which includes a demonstration and a copy of the procedure. The procedure references the string_agg function, a must for any Apex developer. I have added a link to a Blog which offers a good explanation of the function and downloads of the code.

Implementation should be self explanatory, but drop me a line if you need anymore help. I do not profess to being a PLSQL expert so I would be very interested in any ideas of a better way to do it.

Forum Post
String_agg function
SHUNTs solution

Merge into Duncs Tabular Form

Duncs has created an excellent JS based method for generating tabular forms in Apex. The nice part is that additional rows can be added without the need to refresh the page. I try to avoid using separate insert, update and delete statements and prefer the more performant Merge statement.

Duncs method
Demo

The issue is identifying the rows to be deleted in the UPDATE part of the MERGE as they are deleted from the collection and not just flagged for delete. The answer is to use an outer join to join your update table with your tabular form collection. My first few attempts resulted in syntax errors, so after trying various combinations of inline views, I came up with the following:

MERGE INTO emp e
USING (SELECT *
FROM (SELECT to_number(c001) c001
     ,c002 ename
     ,c003 job
     ,c004 mgr
     ,c005 hiredate
     ,f.empno
     ,f.deptno
   FROM (SELECT *
FROM apex_collections
WHERE collection_name = 'EMP')
FULL OUTER JOIN emp f ON to_number(c001) = f.empno)
WHERE deptno = :p1_deptno) c
ON (e.empno = c.empno)
WHEN MATCHED THEN
UPDATE
SET e.ename = c.ename
   ,e.job = c.job
   ,e.mgr = c.mgr
   ,e.hiredate = c.heirdate DELETE
WHERE c.c001 IS NULL
WHEN NOT MATCHED THEN
INSERT
   (e.ename
   ,e.job
   ,e.mgr
   ,e.heirdate
   ,e.deptno)
VALUES
   (c.ename
   ,c.job
   ,c.mgr
   ,c.hiredate
   ,:p1_deptno);

Hiding Columns In Interactive Reports

This problem has been bugging me for ages, how to hide columns when downloading with interactive reports. The answer is simple; for the column you wish to hide set the Conditional Display as follows:

• Condition Type: Request Is NOT Contained within Expression 1


• Expression1: CSV,PDF,RTF,XLS

Cally

Cally is Chinchilla Persian, Blue Persian cross with a bit of tabby thrown in for good grace. I bought her from a lady in Portsmouth who was the girlfriend of a bloke I used to get a lift to work from. Cally was born as the second in a litter of three, the first one out was Kevin, a big cuddly lump of a cat; next was Cally and then came Jazmine a kind of ratty looking thing, not at all like the other two. I had been looking for a pet for some time and although I would consider myself a dog man these kittens looked gorgeous. I liked Cally and Nikki liked Kevin, and rather than argue about it we decided to take both. To cut a long story short I split with Nikki and was lucky enough to end up with both Kevin and Cally.

Kevin and Cally have been my companions for the last 16 years and during that time they have been constant source of joy and love for me. They usually lay on the road outside the house or sit on the driveway waiting for me to come home from work. Each year as winter approached, I have a constant fight with the dreaded knots between their legs caused by their long winter coats. No matter what I’m going though they are always there; always understanding and always have lots of love to give. Even when I slammed Cally’s head in a door, she just looked at me to say ‘what did you do that for’ and then continued on her way.



Cally particularly has always been a good bed friend, especially if she knows I’m down. Just a few days ago, after I fell out with the RAF at work, I awoke to find her curled up on my pillow, snoring away. Interestingly I have never had any problem sleeping with her snoring, but at boarding school and in army barracks it drives me up the wall. My 'happy thought' is to think of Cally curled up in my arms, both of us asleep. That thought has made some pretty miserable situations very bearable. Particularly useful at Sandhurst where it was pretty miserable the whole time.

Cally died in my arms at 6 o’clock tonight after severe kidney failure. The sense of loss I feel is indescribable and I have no idea how to move on with my life. I have had wonderful love and support from my friends, family and Gaily, which is very much appreciated, but at the end of the day I have lost the best friend a man could ever have. Bye bye baby I love you and will miss you terribly.

Drumming Hunt

Wow, no blog for 2 years and then 2 in one day. Perhaps this is a sign of my commitment to spend less time working and more time having fun. Anyway, this is the best clip of my all-time favourite drummer, Mr Johnny Rabb, the free-hand master.