Absence Action History Details Script

Absence Approval Action History Script:


SELECT hats.information1, hats.information2, hats.information6,
       hats.information8, psa.user_name, psa.action
  FROM hr_api_transactions hat,
       hr_api_transaction_steps hats,
       apps.wf_items wi,
       pqh_ss_approval_history psa
 WHERE hat.selected_person_id = 501
   AND hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
   AND hat.transaction_id = hats.transaction_id
   AND wi.item_key = hat.item_key
   AND wi.item_key = psa.transaction_item_key
   AND wi.item_type = 'HRSSA'
   AND end_date IS NOT NULL



Absence Pending History Sctipt:


SELECT wfn.notification_id, wi.item_key,
       TO_DATE (hats.information1, 'YYYY-MM-DD'), hats.information2,
       hats.information6, hats.information8, wfn.recipient_role, wfn.status
  FROM hr_api_transactions hat,
       hr_api_transaction_steps hats,
       apps.wf_items wi,
       apps.wf_notifications wfn
 WHERE hat.selected_person_id = 501
   AND hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
   AND hat.transaction_id = hats.transaction_id
   AND wi.item_key = hat.item_key
--and wi.item_key=psa.TRANSACTION_ITEM_KEY
   AND wi.item_type = 'HRSSA'
   AND wi.end_date IS NULL
   AND wi.item_key = wfn.item_key
   AND wfn.notification_id IN (SELECT MAX (notification_id)
                                 FROM apps.wf_notifications
                                WHERE item_key = wi.item_key)
   AND TO_DATE (hats.information1, 'YYYY-MM-DD') >= '01-JAN-2017'