BI Publisher reports showing stale data?

I have posted a new article on my new blog URL:

http://kumar-oraclebi.blogspot.com/2014/10/bi-publisher-reports-showing-stale-data.html

I will add links on this site to any article I post on new site for few months, to make transition easier.

Changing my Blog site to kumar-oraclebi.blogspot.com

To all my readers:

I am migrating my blog site to a new URL:

http://kumar-oraclebi.blogspot.com/

I will be posting new article to that site going forward. I have copied the content from this site to there. Thank you for visiting my blog site!!

Oracle Openworld 2014

Gaining insight at Oracle Open World 2014. Here are few images from the event.

How to select last updated transaction for every ID from a history table?

Here is the scenario:

You have multiple invoices in a table, with one record everytime the Invoice goes through a process, for example Invoice Created, Invoice Submitted, Invoice Approved etc. On top of that there could be multiple records for a step, for example an Invoice could be approved by multiple approvers, and that will end up being new records in the transaction history table.

If you need to create a report, that will show list of Invoices approved, and name of the last approver and the date, though could be tricky to get. After doing some research I found a simple way of determining that programatically:

We select the invoice along with timestamp of record, and sort them in descending order. We also select row_number for this query. The trick is to filter this result set where row_number is 1. Here is the exact sql if you need to refer:

Select  User_Id, approve_date,  Invoice_Id From (
 Select Sth.User_Id user_id, Sth.Timestamp approve_date, Swpv.Property_Value Invoice_Id,
  Row_Number() Over (Partition By Swpv.Property_Value Order By  To_Char(Sth.Timestamp,'DD-MON-YYYY HH24:MI:SS') Desc) R
  From Sf_Transition_History sth, Sf_Workitem_Property_Values_V swpv
Where Sth.Workitem_Instance_Id = swpv.Workitem_Instance_Id
   And Sth.Event_Name = 'Approved'
   And Swpv.Property_Name = 'InvoiceID'
)
Where R = 1

Table not found in Database

This could be annoying when analyzing databases for available tables in the schema. For quick reference, here is the query that can help locate any table in the database:

Select *

From Dba_Objects 

where lower(object_name) = 'table_name';