Query to get details of start and end date for self service absence submitted via workflow

If you are like me, you need a clean way of querying up details of absences (in R12) that have been submitted via self service, (but not yet approved). This includes querying up the start and end date of absences submitted. The "Status Monitor" (via responsibility Workflow Administrator Web (New) > Administrator Workflow > Status Monitor) gives some, but not all details around absences.

Also, when some of these self service transactions end up in error, you may need to use a query to extract the list of such errors, so that business can enter these transactions manually via Core forms.

The query below should help. Key pointers (so that you can modify the query to suit your needs better):
1) Use the hr_absutil_ss package to get the details of absence transaction. E.g. hr_absutil_ss.getStartDate gets  you the start date of absence submitted via self service.
2) This query uses the seeded package wf_fwkmon to get item status (wf_fwkmon.getitemstatus).This is important to understand. The workflow status can be determined from two places: table HR_API_TRANSACTIONS (HAT), column = STATUS. And also the package wf_fwkmon.getitemstatus. If a transaction is in error (as seen from the Status Monitor page), HAT.STATUS does not necessarily contain a value 'E' (for error). You can have a value of (say) 'Y' here, which indicates there is a problem with approval hierarchy. On the other hand - wf_fwkmon.getitemstatus - is a better representation of which transactions show up as being in 'error' status on Status Monitor page.
See lookup PQH_SS_TRANSACTION_STATUS for codes and meanings used in the column HAT.STATUS. See lookup FND_WF_PROCESS_STATUS_LIST for codes used by wf_fwkmon.getitemstatus.
3) This query does not show cancelled workflows (see condition - AND fl.meaning !='Canceled')

4) This query can be easily extended to other self-service transactions by changing the value of column TRANSACTION_REF_TABLE in table HR_API_TRANSACTIONS. In the query below, this has been coded as 'PER_ABSENCE_ATTENDANCES' to indicate absences.

Ok, so now the query:

SELECT  hat.item_key
       ,s.full_name supervisor
       ,p.employee_number   ,decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',p.global_name,p.local_name) employee_name
       ,hou.name HR_Org
       ,PAAT.name Absence_Type
       ,hr_absutil_ss.getStartDate(hat.transaction_id,null) start_date
       ,hr_absutil_ss.getEndDate(hat.transaction_id,null) end_date
       ,hr_absutil_ss.getAbsenceHoursDuration(hat.transaction_id,null) absence_hours
       ,hr_absutil_ss.getAbsenceDaysDuration(hat.transaction_id,null) absence_days
       ,hr_absutil_ss.getAbsenceCategory(hat.transaction_id,null) absence_category
       --,hr_absutil_ss.getAbsenceType(hat.transaction_id,null) absence_type
       --,to_number(hats.Information5) absence_attendance_type_id
       --,hr_absutil_ss.getApprovalStatus(hat.transaction_id,null) approval_status
       --,hat.TRANSACTION_REF_ID absence_attendance_id
       ,hat.status
 ,wf_fwkmon.getitemstatus(workflowitemeo.item_type,workflowitemeo.item_key,workflowitemeo.end_date,workflowitemeo.root_activity,workflowitemeo.root_activity_version) status
       ,FL.meaning
  FROM  hr_api_transactions hat
       ,hr_api_transaction_steps hats
       ,per_all_people_f p
       ,per_all_assignments_f a
       ,per_all_people_f s
       ,PER_ABSENCE_ATTENDANCE_TYPES PAAT
       ,hr_all_organization_units hou
       ,wf_items workflowitemeo
       ,fnd_lookup_values fl
 WHERE  hat.TRANSACTION_REF_TABLE='PER_ABSENCE_ATTENDANCES'
   AND  hat.TRANSACTION_GROUP='ABSENCE_MGMT'
   AND  hat.TRANSACTION_IDENTIFIER='ABSENCES'
   AND  hat.TRANSACTION_REF_ID is not null
   AND  (trunc(sysdate) between p.effective_start_date and p.effective_end_date)
   AND  hat.SELECTED_PERSON_ID = p.person_id
   AND  hat.transaction_id=hats.transaction_id(+)
   AND  p.person_id = a.person_id
   AND  a.supervisor_id = s.person_id
   AND  a.organization_id = hou.organization_id(+)
   AND  (trunc(sysdate) between a.effective_start_date and a.effective_end_date)
   AND  (trunc(sysdate) between s.effective_start_date and s.effective_end_date) 
   AND  PAAT.ABSENCE_ATTENDANCE_TYPE_ID = to_number(hats.Information5)
   AND  hat.item_key = workflowitemeo.item_key
   AND  fl.lookup_type = 'FND_WF_PROCESS_STATUS_LIST'
   AND  fl.lookup_code = wf_fwkmon.getitemstatus (workflowitemeo.item_type,workflowitemeo.item_key,workflowitemeo.end_date,workflowitemeo.root_activity,workflowitemeo.root_activity_version)
/**** Optional Section - START ****/
   AND  fl.meaning !='Canceled' --do not show Cancelled workflows
   --AND hat.status = 'E'--('D','E','AC')  -- show only errored workflows (this will not show workflows where approval hierarchy has an error. See lookup - PQH_SS_TRANSACTION_STATUS for meanings
   AND  wf_fwkmon.getitemstatus (workflowitemeo.item_type,workflowitemeo.item_key,workflowitemeo.end_date,workflowitemeo.root_activity,workflowitemeo.root_activity_version)='ERROR'  -- this shows workflows in error, including ones errored because of approval hierarchy
   --AND hat.SELECTED_PERSON_ID = 36462
   --AND p.employee_number = '258244'
   --AND hat.item_key in (112041, 112042, 112040)
   --AND p.business_group_id = 82
   --AND hr_absutil_ss.getStartDate(hat.transaction_id,null) between '01-jan-2014' and '31-jan-2014'
   --AND not (hr_absutil_ss.getabsencetype(hat.transaction_id,null) is null and hat.status='W')
/**** Optional Section - END ****/
ORDER BY supervisor, employee_name, start_date;