Where did my Planning Job Console History Go?

Quick post on getting purged history from the Planning Job Console...

Planning has a nice feature which is the Job Console. In the Job Console page (Tools-> Job Console) you can check the status (processing, completed, or error) of these job types: Business Rules, Clear Cell Details, Copy Data, Push Data and Planning Refresh.

By default, the job console will delete all information on  any completed jobs older than 4 days. This isn't good if you need to troubleshoot an issue or check a transaction completed before this period. However, the history gets moved to another table in the Planning repository called HSP_HISTORICAL_JOB_STATUS. The table stores the user accounts as objectIDs so we can join it with the HSP_OBJECTS table to get the job history with friendly userIDs.

Here is the SQL to retrieve all the history:

select HSP_HISTORICAL_JOB_STATUS.JOB_ID,

HSP_HISTORICAL_JOB_STATUS.PARENT_JOB_ID,

HSP_HISTORICAL_JOB_STATUS.JOB_NAME,

HSP_HISTORICAL_JOB_STATUS.JOB_TYPE,

HSP_Object.OBJECT_NAME,

HSP_HISTORICAL_JOB_STATUS.START_TIME,

HSP_HISTORICAL_JOB_STATUS.END_TIME,

HSP_HISTORICAL_JOB_STATUS.RUN_STATUS,

HSP_HISTORICAL_JOB_STATUS.DETAILS,

HSP_HISTORICAL_JOB_STATUS.ATTRIBUTE_1,

HSP_HISTORICAL_JOB_STATUS.ATTRIBUTE_2,

HSP_HISTORICAL_JOB_STATUS.PARAMETERS,

HSP_HISTORICAL_JOB_STATUS.JOB_SCHEDULER_NAME

from HSP_HISTORICAL_JOB_STATUS

INNER JOIN HSP_Object

ON HSP_Object.OBJECT_ID=HSP_HISTORICAL_JOB_STATUS.USER_ID

ORDER BY HSP_HISTORICAL_JOB_STATUS.END_TIME

and this is what the output looks like:

To save you from having to do this you can increase the length of history that is stored in the HSP_JOB_STATUS from the default of 4 days to whatever you choose by setting the JOB_STATUS_MAX_AGE in the Planning application properties. All the details can be found here:

http://docs.oracle.com/cd/E57185_01/PLAAG/ch02s08s08.html