1. To get all the leaves applied by employee with item_keys.
the same can be used for Valueset parameters.
SELECT
pat.NAME leave_name,
NVL (information1, information3) date_start,
NVL (information2, information4) date_end,
information8 DURATION
,hrat.item_key, hrat.item_type
FROM pqh_ss_transaction_history hrat, pqh_ss_step_history hrst,PER_ABS_ATTENDANCE_TYPES_TL pat
WHERE hrat.transaction_history_id = hrst.transaction_history_id
and pat.ABSENCE_ATTENDANCE_TYPE_ID = information5
AND LANGUAGE = USERENV('LANG')
and selected_person_id = 26876 --pass the person_id here to get all the transactions for the employee
order by NVL (information1, information3) desc
2. Details of Employee and Leave.
SELECT --hrat.transaction_history_id,
employee_number, full_name,
haou.name org_name,
pat.NAME leave_name,
NVL (information1, information3) date_start,
NVL (information2, information4) date_end, --information5 leave_type,
information8 DURATION, selected_person_id, hrat.item_key
--FROM hr_api_transactions hrat, hr_api_transaction_steps hrats
FROM pqh_ss_transaction_history hrat
,pqh_ss_step_history hrst
,PER_ABS_ATTENDANCE_TYPES_TL pat
,per_all_people_f papf
,per_all_assignments_f paaf
,hr_all_organization_units haou
WHERE hrat.transaction_history_id = hrst.transaction_history_id
and hrat.item_key = '14791'--from step 1
and hrat.ITEM_TYPE = :p_item_type and hrat.ITEM_KEY = :p_item_key
and pat.ABSENCE_ATTENDANCE_TYPE_ID = information5
AND LANGUAGE = USERENV('LANG')
and papf.person_id = paaf.person_id
and papf.person_id = selected_person_id
and fnd_conc_date.string_to_date(NVL (information1, information3)) between papf.effective_start_date and papf.effective_end_date
and fnd_conc_date.string_to_date(NVL (information1, information3)) between paaf.effective_start_date and paaf.effective_end_date
and primary_flag = 'Y'
and haou.organization_id = paaf.organization_id
3. Status of workflow
p_item_type, p_item_key from step 1
SELECT rownum l_sequence ,a.l_notification_id ,a.l_role role ,a.l_action action ,a.l_comments ,to_char(a.l_date,'dd-mm-yyyy hh:mi:ss am') l_date FROM (
SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname
(nvl (wn.more_info_role ,wn.recipient_role)) l_role ,
decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING')) ,
decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action ,
na.text_value l_comments ,ias.end_date l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
,wf_item_activity_statuses ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
UNION
SELECT pah.notification_id l_notification_id ,wf_directory.getroledisplayname (pah.user_name) l_role
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action)) l_action ,pah.user_comment l_comments
,pah.last_update_date l_date FROM pqh_ss_approval_history pah WHERE pah.transaction_item_type = :p_item_type
AND pah.transaction_item_key = :p_item_key AND pah.action NOT IN ('APPROVED','REJECTED','SUBMIT' ,'RFC','RESUBMIT','TIMEOUT','QUESTION','ANSWER')
UNION
SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (wn.recipient_role) l_role
,decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING'))
,decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action
,decode (wn.status ,'CANCELED' ,NULL ,nvl (na.text_value ,
( SELECT user_comment FROM wf_comments WHERE notification_id = wn.notification_id AND action = l.lookup_code AND rownum = 1 ))) l_comments
,nvl (ias.end_date ,ias.begin_date) l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
,wf_item_activity_statuses_h ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
UNION
SELECT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (nvl (wn.more_info_role ,wn.recipient_role)) l_role
,decode (wn.status ,'CANCELED' ,decode (pah.action ,'TIMEOUT' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))
,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN'))) ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))) l_action
,decode (pah.notification_id ,wn.notification_id ,pah.user_comment ,NULL) l_comments ,pah.last_update_date l_date
FROM pqh_ss_approval_history pah ,wf_notifications wn
WHERE pah.transaction_item_type = :p_item_type AND pah.transaction_item_key = :p_item_key
AND pah.action IN ('TIMEOUT','RESUBMIT') AND wn.group_id = ( SELECT group_id FROM wf_notifications WHERE notification_id = pah.notification_id
AND rownum = 1 )
UNION select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role
-- ,WF_CORE.TRANSLATE(C.ACTION) l_action
,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES_H IAS, WF_COMMENTS C, wf_notifications wn
where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id
and wn.notification_id = c.notification_id and C.ACTION in('QUESTION', 'ANSWER')
UNION select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role -- ,WF_CORE.TRANSLATE(C.ACTION) l_action
,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES IAS, WF_COMMENTS C, wf_notifications wn
where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id and wn.notification_id = c.notification_id
and C.ACTION in('QUESTION', 'ANSWER')
UNION SELECT 0 ,wf_directory.getroledisplayname (owner_role) l_role ,wf_core.translate ('SUBMIT')
,appr.text_value note ,begin_date l_date
FROM wf_items i ,wf_item_attribute_values appr
WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND i.item_type = appr.item_type (+) AND i.item_key = appr.item_key (+)
AND appr.name (+) = 'SUBMIT_COMMENTS' ORDER BY l_notification_id ,l_date ) a
