| Oracle HRMS Workflow reporting from SQL [message #257212] |
Tue, 07 August 2007 15:11  |
sbattisti
Messages: 39
Registered: June 2005
|
Member |
|
|
Does anyone know how I could write a report at the SQL level to return a count of HR manager self service transactions submitted in a particular date range?
I know that transactions in progress are stored in hr_api_transactions, but after they are completed, they are removed from that table, so I'm not sure how to report on them.
I have something like this for the currently active transactions in that table:
select count(item_key) as transactions
,decode(process_name,'HR_TERMINATION_JSP_PRC','Termination','HR_MANAGER_JSP_PRC','Change Manager','HR_TRANSFER_JSP_PRC','Change Job') as type
from apps.hr_api_transactions
where item_type = 'HRSSA'
and process_name in ('HR_TERMINATION_JSP_PRC','HR_MANAGER_JSP_PRC','HR_TRANSFER_JSP_PRC')
and creation_date between
to_date('07/01/2007', 'MM/DD/RRRR') AND
to_date('07/31/2007', 'MM/DD/RRRR')
group by decode(process_name,'HR_TERMINATION_JSP_PRC','Termination','HR_MANAGER_JSP_PRC','Change Manager','HR_TRANSFER_JSP_PRC','Change Job')
This returns a nice count, but as I said, this only includes transactions that were never completed successfully.
Ideas?
Steve
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
HTH